Excel Table : add blank columns and headers vba

Question

I have an excel table and I want to add a column to it and then name the header. Somehow my old codes don't work anymore, and I am not sure if it has anything to do with me making the data range a table (Name is Table1). Now the codes run through with no error but only add the blank column but did not add a header. I pressed F8 and it looks like it skips Select portion and jumps to the end.

Table is specified from column A to column AG. I used to be able to insert multiple blank columns and add the headers just fine. Now I don't want to specify cell position and assign a cell value to it (cell(1, 32).value ="Month") since I want to have the flexibility to be add however many columns I need and add the header respectively like before.

Sub Ins_Blank_Cols_Add_Headers()
 Columns("AF:AG").Insert Shift:=xlToRight
 Columns("AF:AF").Cells(1, 1) = "<<NewHeader>>"
 Columns("AG:AG").Cells(1, 1) = "<<NewHeader>>"
    Dim cnter As Integer

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
cnter = 0

For i = 1 To LastCol
  If Cells(1, i) Like "<<NewHeader>>*" Then
        Select Case cnter
            Case 0: Cells(1, i).Value = "Month"
            Case 1: Cells(1, i).Value = "Year"
        End Select
        cnter = cnter + 1
    End If
Next i

End Sub

Not sure what to tell you. At this point, you're adding a column at AF (shifting to right and remapping to AG), then labelling the blank column's Row 1. I ran the code myself and it is doing just that. Have you checked your references? I have "Visual Basic for Applications", "Mocrosoft Excel 14.0 Object Library", "OLE Automation", and "Microsoft Office 14.0 Object Library." It's at least a start! — Cyril
If you guys know a different way around it, please share, I am new to VBA. — sc1324

cyboashu cyboashu · Accepted Answer · 2017-01-06T15:54:12

Stop using Range.Select and Selection, it will do you good.

Also always use fully qualified ranges.

Your issue is that when you insert a column in Table, Excel automatically assigns a header name to it.

So your IF condition to check empty fails. Hence No Select case invocation

Change this

Columns("AF:AF").Select
Selection.Insert Shift:=xlToRight

To

 Columns("AF:AF").Insert Shift:=xlToRight
 Columns("AF:AF").Cells(1, 1) = "<<NewHeader>>" ' In Table Can't set blank header so use a place holder.

Also Change the If Condition from

If IsEmpty(Cells(1, I)) Then

To

 If Cells(1, I) Like "<<NewHeader>>*" Then

Excel Table : add blank columns and headers vba

1 Answers