Excel Table Size in Macro

Copper Contributor

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.

6 Replies

@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"

@WayneEK 

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 Sub

You 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"

 

@WayneEK 

Using the CurrentRegion property, you may try something like this...

 

 

 

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table1"

 

 

 

 

 

@Smitty Smith Thanks so much. Several responded with the same info and it worked. Very much appreciated.

@Subodh_Tiwari_sktneer Thanks so much. Several responded with the same info and it worked. Very much appreciated.

@Takmil Thanks so much. Several responded with the same info and it worked. Very much appreciated.