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

Question

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.

AAA · Accepted Answer · 2019-06-30 21:20:43Z

1

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

answered Jun 30, 2019 at 21:20

AAA

3,6801 gold badge18 silver badges33 bronze badges

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

1 Comment

Andy Fazulus Over a year ago

Thanks mate. Using .Rows(i).Value works too, but I think I'll use .Cells(i).Value as you suggested.

AsUsual · Accepted Answer · 2019-07-01 08:52:41Z

0

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

edited Jul 1, 2019 at 8:52

answered Jul 1, 2019 at 6:57

AsUsual

5342 silver badges9 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related