Dynamic Size for Tables Created in VBA

[abmyers]

I'm a VBA rookie.

I have this code from recording. All I'm doing is selecting all data, and making it into a table. Problem is the amount of data, in terms of rows, always changes (columns are constant). How can I make this dynamic so it counts the number of rows and makes the table accordingly.

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AC$385"), , xlYes).Name = _
"Table1"

Problem is the hard-coded "$AC$538" thanks!

[Jakobshavn]

Without seeing the Table, try replacing:

Range("$A$1:$AC$385")

with:

Please Login or Register  to view this content.

[abmyers]

It sort of worked, but this, which I didnt include right below the code now doesnt work:

ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Table1" (error 1004)

Thanks for the help

[Jakobshavn]

Looks like we need to Set rng to something somewhere?

[abmyers]

Here is more code if it helps

Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Table1"
Range("Table1[[#Headers],[Ask Sz]]").Select
ActiveCell.FormulaR1C1 = "Ask Sz"
Range("Table1[[#Headers],[Ask Sz]]").Select

[abmyers]

Nevermind, I re-recorded so that line is gone. Thanks for the help.