Forum Discussion
jesshenry
Jul 11, 2022Copper Contributor
Creating Table with Varying Number of Rows in VBA
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"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight13"
ActiveSheet.ListObjects("Table1").ShowTableStyleRowStripes = False
Rows("1:1").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0Then I tried selecting the cells in row 1 for each column and doing Ctrl+Shift+Down but the code locked in the range.
Range("A1:K1").Select
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?
No RepliesBe the first to reply