question

BubnaShreya-0514 avatar image
0 Votes"
BubnaShreya-0514 asked ·

VBA Change Data Type of Excel Table Columns

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.

office-vba-dev
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.