Q:

Excel VBA: Adding columns to ListObject wihtout adding Columns to Worksheet

I'm running VBA code in Excel to build a table (ListObject) after clicking a button.
The columns will be dynamically added by using a collection for the header names.
Before building the table, the existing table is deleted to avoid errors.
The problem with using ListColums.Add is that it will add columns to the whole sheet and push the existing columns to the right. So everytime you click the button, the sheet will have more and more columns. This is not very clean. The button will be pressed very frequently and I want to avoid pushing the limits of Excel Worksheets. My build function works as follows:
Function buildTable(wsName As String, tblName As String, clmns As Collection, Optional tableRange As String)

    ' * If Range is not specified choose "$A$1"
    If IsMissing(tableRange) Then
        position = "$A$1"
    End If

    ' * Build the table based on the passed parameters
    ThisWorkbook.Worksheets(wsName).ListObjects.Add(xlSrcRange, ThisWorkbook.Worksheets(wsName).Range(tableRange), , xlYes).Name = tblName

    ' * Declare and define tblName as ListObject in Worksheet wsName
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets(wsName).ListObjects(tblName)

    ' * Declare and define the columns of table tblName
    Dim clmn As ListColumns
    Set clmn = tbl.ListColumns

    ' * Replace the first column name with the first item in our column Collection clmns
    tbl.HeaderRowRange(1, 1).Value = clmns.Item(1)

    ' * Now loop through the rest of the passed header name collection clmns. Start with 2, because 1 was already set in the last step
    X = 2

    For X = 2 To clmns.Count
        ' * Add Item X to the table (String from the passed collection clmns) as column.
        clmn.Add.Name = clmns(X)
    Next X

End Function

I'll call it like this:
' * Declare and fill my Collection of headers
Dim clHeaders As New Collection
clHeaders.Add "MyFirstColumn"
clHeaders.Add "MySecondColumn"
clHeaders.Add "MyThirdColumn"

' * Call the builTable Function
Call buildTable("Sheet1", "MyTable", clHeaders)

I already tried ThisWorkbook.Worksheets("Sheet1").Columns("AS").EntireColumn.Delete, but this only empties the already empty columns and doesn't delete them...
Is there a way to do this without getting more and more columns at the end of the sheet every time you click the button?

A:

Ok, Rory had a point.
There are no Columns added, but the viewspace keeps growing (scrollbar shrinks).
So all I had to do is ThisWorkbook.Worksheets(wsName).UsedRange.SpecialCells (xlCellTypeLastCell) which I found here
Thanks!

