VBA Change Data Type of Excel Table Columns

Question

Hello,

I have an Excel Table called "ABC" with a number of Percent Columns "GH%" , "AD%" etc.

I want to write a code which would change the Data type of these Percent Columns which have "%" in their Column headers to type Percentage.

Kindly help with this. Thank you.

Answer 1

Try this code:

Dim ws As Worksheet
Set ws = Sheet1 ' TODO: use the correct worksheet

Dim t As ListObject
Set t = ws.ListObjects("ABC") ' TODO: use the correct table name

Dim c0
For Each c0 In t.ListColumns
    Dim c As ListColumn
    Set c = c0

    If Right(c.Name, 1) = "%" Then
        Dim r As Range
        Set r = c.DataBodyRange
        r.NumberFormat = "0""%"""
        ' or maybe r.NumberFormat = "0%"
    End If
Next

Adjust it according to your needs. Backup the file before experimenting.