Insert New Column in Table Excel VBA

Question

I'm running into some issues on a worksheet that I'm building. I want to insert a column at a specific location in a table and then set the header.

I searched around for a while, and found (some version of) the following code:

ActiveSheet.Columns(2).Insert

This works to insert the column, but I'm not sure how to change the header of the table after this.

Also, I previously got some advice here on adding columns to the end of a table and naming them.

The code for that is:

Dim oSh As Worksheet
Set oSh = ActiveSheet
Dim oLc As ListColumn
Set oLc = oSh.ListObjects("PropTable").ListColumns.Add

oLc.Name = "XYZ"

I tried combining these two methods in various ways, but I'm not having any luck. Is there any way to modify the second block of code so that it inserts a column in a specific location, rather than just adding a column?

Thanks.

-Sean

Answer 1

It is possible to add a column to a table in a particular place and name it, using the same line of code.

Table.ListColumns.Add(2).Name = "New Header"

This will add a column to the left of the second column in the table and name it New Header. You can make your code dynamic by adding a column to the left of one that you know the name of. This way, it is not necessary to specify the integer value of a new column's fixed location.

Dim newColNum as Integer
newColNum = Range("Table[Column Name]").Column
Table.ListColumns.Add(newColNum).Name = "New Header"

[Column Name] is the name of the column in your table where you want to insert a new column. It can have any position in the table, and you can pass it's value as an integer to the Add.

Answer 2

I know the thread is old, but I must point out that the most upvoted answer here is risky and can get you in a serious trouble. I don't know if it depends Excel version - I use Excel'16.

Let's consider table containing columns: col A, col B and col C.

We use "The Dudes" one-liner code and want to name our new column "Col B". It already exists but check what happens:

Sub theDude()

  Dim Table As ListObject
  Set Table = ActiveSheet.ListObjects(1)
  
  With Table
  
    ' adding column on the second place
    ' and trying to force its header to "Col B"
    .ListColumns.Add(2).Name = "Col B"
    
    'fill "Col B" with value
    .ListColumns("Col B").DataBodyRange = "test"
    
  End With
  
End Sub

And what we get? In result we have 4 columns:

• Col A
• New inserted Column1 or another DEFAULT NAME of Table's column (1)
• Col B - the "old" B column filled with "test" string
• Col C

(1) it depends on yours language version- mine is called Kolumna1 and it's given by Excel automatically

The worst thing is our data in Col B is lost after macro run. So I would suggest instead one-liner (methods chaining) use @stenci's step by step solution, or even better add some error handling e.g.:

Sub AddingColumn()

  Dim Table As ListObject
  ' ActiveSheet just for test
  Set Table = ActiveSheet.ListObjects(1)   
  
  Dim newColName As Variant     ' or string / long
      newColName = "Col B"
      
  If headerExists(newColName, Table) Then
 
    Dim tit As String:  tit = "Error"
    Dim txt As String
        txt = "Header " & newColName & " already exists. Macro will be interrupted"

        MsgBox txt, vbOKOnly, tit
        Exit Sub
    
  Else
    
    ' main code goes here *********************
    With Table
      ' adding column on the second place
      ' and trying to force its header to "Col B"
        .ListColumns.Add(2).Name = newColName
      'fill "Col B" with value
        .ListColumns("Col B").DataBodyRange = "test"
    End With
    
  End If
  
End Sub

Function headerExists(ByVal findHeader As String, ByVal tbl As ListObject) As Boolean
    
    Dim pos As Variant     ' position
        pos = Application.Match(findHeader, tbl.HeaderRowRange, 0)
        
        headerExists = Not IsError(pos)

End Function

Answer 3

  Dim Table As ListObject
  Set Table = Sheet1.ListObjects("Table1")
  Table.ListColumns.Add 2
  Table.HeaderRowRange(2) = "New header"