Excel VBA - Add new table columns with specific header names

Question

I have a simple vba question. I want a macro that will add exactly 4 new columns in my table object, ("Table1"). I would also like these to be named in order, from left to right:

AHT, Target AHT, Transfers, Target Transfers

The code I have below adds the columns just fine, but I am not sure how to name one individually. Also, could someone please show me how to loop that section of code. Thanks!

Sub insertTableColumn()
Dim lst As ListObject
Dim currentSht As Worksheet

Set currentSht = ActiveWorkbook.Sheets("Sheet1")
Set lst = ActiveSheet.ListObjects("Table1")

'below is the code that I would like to have looped
    lst.ListColumns.Add
    lst.ListColumns.Add
    lst.ListColumns.Add
    lst.ListColumns.Add

End Sub

user4039065 · Accepted Answer · 2016-06-01 20:11:01Z

2

A variant array is a good place to store the variables in a looped sequence.

Sub insertTableColumn()
    Dim lst As ListObject
    Dim currentSht As Worksheet
    Dim h As Long, hdrs As Variant

    hdrs = Array("AHT", "Target AHT", "Transfers", "Target Transfers")

    Set currentSht = ActiveWorkbook.Sheets("Sheet1")
    Set lst = ActiveSheet.ListObjects("Table1")

    With lst 'ActiveSheet.ListObjects("Table1")
        For h = 0 To 3
            .ListColumns.Add
            .ListColumns(.ListColumns.Count).Name = hdrs(h)
        Next h
    End With

End Sub

When creating an array of strings in this manner, remember that the variant array's index is zero-based by default.

answered Jun 1, 2016 at 20:11

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

Andy Over a year ago

Thanks for the bit about the zero-based index. I certainly would have questioned the 0.

casewolf Over a year ago

I know this is old, but you can avoid the whole "0-based" issue if you change the hard coded: For h = 0 To 3 to the following: For h = Lbound(hdrs) to Ubound(hdrs) . This has the advantage of only having to maintain what goes into your array.

Dave · Accepted Answer · 2016-06-01 20:15:46Z

2

The following code creates an array of the column names you want to add, then loops as many times as there are headers to add and sets the name at the same time.

Sub insertTableColumn()
Dim lst As ListObject
Dim currentSht As Worksheet

Set currentSht = ActiveWorkbook.Sheets("Sheet1")
Set lst = ActiveSheet.ListObjects("Table1")
ColumnNames = Array("AHT", "Target AHT", "Transfers", "Target Transfers")

' below is the code that I would like to have looped
For iLoop = 0 to UBound(ColumnNames)
    Set oLC = lst.ListColumns.Add
    oLC.Name = ColumnNames(iLoop)
Next

End Sub

answered Jun 1, 2016 at 20:15

Dave

4,3562 gold badges26 silver badges33 bronze badges

2 Comments

Andy Over a year ago

Thank you very much for that introduction to the UBound Function. I had not run into that before. Just a quick general question - If I wanted to declare your "ColumnNames" variable, how would I do so? I tried to declare it as a string and my code generated an error. What is best practice for declaring variables you are going to use as an array? Thanks again!

Dave Over a year ago

You can just use Dim ColumnNames As Variant (I should have added that line to be honest), but if you don't specify what a variable type is, VBA will automatically define it as Variant, which will accept pretty much any type really, so you could get away with simply Dim ColumnNames :)

Collectives™ on Stack Overflow

Excel VBA - Add new table columns with specific header names

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related