Creating Table with Varying Number of Rows in VBA

New Contributor

Hello! First time posting here. I posted this on another website's forum but haven't had any luck and was hoping someone here may have the answer I'm looking for.


I need to turn my data into a table as part of my VBA. The data will always have the same columns but the rows will vary. I used the macro recorder to try it but I didn't get the desired result. Here's what I've tried so far:


First I tried the below but the code locked in the range.

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K$13200"), , xlYes).Name = "Table1"
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight13"
    ActiveSheet.ListObjects("Table1").ShowTableStyleRowStripes = False
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0

Then I tried selecting the cells in row 1 for each column and doing Ctrl+Shift+Down but the code locked in the range.

    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K$13200"), , xlYes).Name = "Table1"

I looked online and watched videos and much of it described writing code to actually create the table from scratch but that didn't seem right. Is there an easier way?

0 Replies