Forum Discussion
Excel Table Size in Macro
Hi, I download external Excel information and run a macro I created for it. It works great.
However, the external info I download grows every week with new rows and when I run the macro, the new rows dont get included in the table.
This is part of my macro to turn it into a formatted table:
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$X$125"), , xlYes).Name ="Table1"
So, it creates a table and formats it with 125 rows.
But next week, there may be 130 rows, not 125 rows. Since the Macro only goes to row 125, the table stops at row 125 and misses the last 5 rows.
Question: Other than setting the table size in the macro from "$A$1:$X$125" to something like "$A$1:$X$1200", how can you select all the rows (including the new 5 rows) automatically without making the table so large by using ("$A$1:$X$1200")?
Thanks, Wayne.
Using the CurrentRegion property, you may try something like this...
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table1"Subodh_Tiwari_sktneer Thanks so much. Several responded with the same info and it worked. Very much appreciated.
- Smitty Smith
Microsoft
WayneEK You can set a variable to capture the last row of data then plug that in.
Dim lr as Long lr = Cells(Rows.Count,"A").End(xlUp).Row ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$X$" & lr), , xlYes).Name ="Table1"Smitty Smith Thanks so much. Several responded with the same info and it worked. Very much appreciated.
You can define a variable that contains the row number of the last row of the data. In this example I take the last populated row in column X:
Sub Something() Dim lastrow As Long
lastrow = Cells(Rows.Count, 24).End(xlUp).Row
End SubYou can then use this variable in other portions of the code. e.g.
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$X$" & lastrow), , xlYes).Name ="Table1"
