Add column to selected tables in Excel worksheet

Question

I have a worksheet with 10 tables (named Table1, Table2, etc). I would like to add a column to some of the tables only (e.g., Table1, Table7 and Table9). The code below successfully works to add a column to all the tables in the worksheet. Any advice?

Sub LoopThroughMyTables()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject

    For Each tbl In ws.ListObjects
        'add a column to the left of the Average column
        Dim newColNum As Integer
        newColNum = Range("CshwsTbl[Average]").Column
        tbl.ListColumns.Add(newColNum).Name = "NewColumn"
    Next tbl
End Sub

It does not look as if it would add a column to any table, it seems to be adding a row. — JohnRC, Jan 28 '18 at 22:19
Very sorry. Pasted the wrong code by mistake. Thanks for pointing it out! — jtorrance, Jan 28 '18 at 23:38
Possible duplicate of [Insert New Column in Table Excel VBA](https://stackoverflow.com/questions/17549885/insert-new-column-in-table-excel-vba) — 41686d6564 stands w. Palestine, Jan 29 '18 at 00:07

dev1998 · Answer 1 · 2018-01-30T03:38:22.613

0

Try this:

Sub LoopThroughMyTables()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject

    For Each tbl In ws.ListObjects
        If tbl.Name = "Table1" Or tbl.Name = "Table7" Or tbl.Name = "Table9" Then 
            'add a column to the left of the Average column
            Dim newColNum As Integer
            newColNum = Range("Table1[Average]").Column
            tbl.ListColumns.Add(newColNum).Name = "NewColumn"
        End If
    Next tbl

End Sub

If you have lots of tables on your sheet, this would not be the most efficient way. But it is simple and easy to understand.

Alternate code using String array:

Sub LoopThroughMyTables()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject

    Dim tables(3) As String
    tables(0) = "Table2"
    tables(1) = "Table7"
    tables(2) = "Table9"

    For Each tbl In ws.ListObjects
        If IsInArray(tbl.Name, tables) Then 
            'add a column to the left of the Average column
            Dim newColNum As Integer
            newColNum = Range("Table1[Average]").Column
            tbl.ListColumns.Add(newColNum).Name = "NewColumn"
        End If
    Next tbl

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

I found the IsInArray function here: How to find if an array contains a string

edited Jan 30 '18 at 03:38

answered Jan 29 '18 at 17:00

dev1998

882
7
17

That works as expected and is easy to understand. But out of curiosity (and because of frustrated attempts at same) how would you do the same thing using something like a list or array of Table names? – jtorrance Jan 30 '18 at 02:19
@Jahbless I added a string array search for Table names. – dev1998 Jan 30 '18 at 03:39
Thanks! If they'll let me accept an answer, I'll accept yours! – jtorrance Jan 30 '18 at 22:36
I'm happy to help. Why can't you select an answer? – dev1998 Jan 30 '18 at 23:11
The code in the answer above will not work as described. The first time through the FOR loop, "New Column" will be inserted to the LEFT of the "Average" column but on each subsequent column add, "New Column" will be to the RIGHT of the "Average" column. This is because of the static use of Table1. Instead of the line: newColNum = Range("Table1[Average]").Column substitute it with newColNum = Range(tbl.Name & "[Average]").Column – The Dude Jun 14 '18 at 07:11

Add column to selected tables in Excel worksheet

1 Answers1