CODEWITHSUNDEEP
vba
Andy Fazulus
Andy Fazulus

Reputation: 45

How to add new column into table based on header title instead of position?

I want to add a new column into a table after a certain header title in which the position will vary from time to time.

I can use this code to add a column at position 4 but the position depends on the header title which may changes.

Any clue on how to go on about this?

Worksheets(1).ListObjects("Table1").ListColumns.Add Position:=4

E.g.: Header title is in position 5, the code will add a new column in position 6. E.g.: Header title is now in position 7, the code will add a new column in position 8.

Upvotes: 0

Views: 525

Answers (2)

AsUsual
AsUsual

Reputation: 524

This should work, just change "YourHeader" to your header name

Sub AddColAfterHeader()
    [Table1[YourHeader]].Next.EntireColumn.Insert
End Sub

..or this if the surroundings matter

Sub AddColAfterHeader2()
    ActiveSheet.ListObjects("Table1").ListColumns.Add _
    ([Table1[YourHeader]].Next.Column - [Table1].Column + 1)
End Sub

Upvotes: 0

AAA
AAA

Reputation: 3670

We can use the HeaderRowRange property of the ListObject to solve this:

Dim HeaderTitle as String, i as Integer, MyTable as ListObject

HeaderTitle = "YourTitleGoesHere" 'change to match your title
Set MyTable = Worksheets(1).ListObjects("Table1")
With MyTable.HeaderRowRange
    For i = 1 to .Count
        If .Cells(i).Value = HeaderTitle Then
            MyTable.ListColumns.Add Position:= i + 1
            Exit For
        End If
    Next i
End With

Upvotes: 1

Related Questions