CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

Excel Table : add blank columns and headers vba

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

Upvotes: 0

Views: 3007

Answers (1)

cyboashu
cyboashu

Reputation: 10433

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

Upvotes: 1

Related Questions