Home

Excel Table Size in Macro

%3CLINGO-SUB%20id%3D%22lingo-sub-893766%22%20slang%3D%22en-US%22%3EExcel%20Table%20Size%20in%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893766%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20download%26nbsp%3Bexternal%20Excel%26nbsp%3Binformation%20and%20run%20a%20macro%20I%20created%20for%20it.%20It%20works%20great.%3C%2FP%3E%3CP%3EHowever%2C%20the%20external%20info%26nbsp%3BI%20download%26nbsp%3Bgrows%26nbsp%3Bevery%20week%20with%20new%20rows%20and%20when%20I%20run%20the%20macro%2C%26nbsp%3Bthe%20new%20rows%20dont%20get%20included%20in%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20part%20of%20my%20macro%20to%20turn%20it%20into%20a%20formatted%20table%3A%3C%2FP%3E%3CP%3EActiveSheet.ListObjects.Add(xlSrcRange%2C%20Range(%22%24A%241%3A%3CSTRONG%3E%24X%24125%3C%2FSTRONG%3E%22)%2C%20%2C%20xlYes).Name%20%3D%22Table1%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20it%20creates%20a%20table%20and%20formats%20it%20with%20%3CSTRONG%3E125%20rows%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EBut%26nbsp%3Bnext%20week%2C%26nbsp%3Bthere%20may%20be%3CSTRONG%3E%20130%3C%2FSTRONG%3E%20%3CSTRONG%3Erows%3C%2FSTRONG%3E%2C%20not%20%3CSTRONG%3E125%3C%2FSTRONG%3E%20%3CSTRONG%3Erows%3C%2FSTRONG%3E.%20Since%20the%20Macro%20only%20goes%20to%20%3CSTRONG%3Erow%26nbsp%3B125%3C%2FSTRONG%3E%2C%20the%20table%20stops%20at%20%3CSTRONG%3Erow%26nbsp%3B125%3C%2FSTRONG%3E%20and%20misses%20the%20last%205%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuestion%3A%20Other%20than%20setting%20the%20table%20size%20in%20the%20macro%20from%26nbsp%3B%22%24A%241%3A%3CSTRONG%3E%24X%24125%3C%2FSTRONG%3E%22%20to%20something%20like%20%22%24A%241%3A%3CSTRONG%3E%24X%241200%3C%2FSTRONG%3E%22%2C%26nbsp%3Bhow%20can%20you%20select%20all%20the%20rows%20(including%20the%20new%205%20rows)%20automatically%20without%20making%20the%20table%20so%20large%20by%20using%20(%22%24A%241%3A%3CSTRONG%3E%24X%241200%3C%2FSTRONG%3E%22)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20Wayne.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-893766%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Emacro%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETables%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893828%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Size%20in%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893828%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420240%22%20target%3D%22_blank%22%3E%40WayneEK%3C%2FA%3E%26nbsp%3BYou%20can%20set%20a%20variable%20to%20capture%20the%20last%20row%20of%20data%20then%20plug%20that%20in.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EDim%20lr%20as%20Long%0A%0Alr%20%3D%20Cells(Rows.Count%2C%22A%22).End(xlUp).Row%0A%0AActiveSheet.ListObjects.Add(xlSrcRange%2C%20Range(%22%24A%241%3A%24X%24%22%20%26amp%3B%20lr)%2C%20%2C%20xlYes).Name%20%3D%22Table1%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893829%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Size%20in%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893829%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420240%22%20target%3D%22_blank%22%3E%40WayneEK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20define%20a%20variable%20that%20contains%20the%20row%20number%20of%20the%20last%20row%20of%20the%20data.%20In%20this%20example%20I%20take%20the%20last%20populated%20row%20in%20column%20X%3A%3C%2FP%3E%3CPRE%3ESub%20Something()%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20lastrow%20As%20Long%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20lastrow%20%3D%20Cells(Rows.Count%2C%2024).End(xlUp).Row%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3EYou%20can%20then%20use%20this%20variable%20in%20other%20portions%20of%20the%20code.%20e.g.%3C%2FP%3E%3CPRE%3EActiveSheet.ListObjects.Add(xlSrcRange%2C%20Range(%22%24A%241%3A%3CSTRONG%3E%24X%24%3C%2FSTRONG%3E%22%20%26amp%3B%20lastrow)%2C%20%2C%20xlYes).Name%20%3D%22Table1%22%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893921%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Size%20in%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420240%22%20target%3D%22_blank%22%3E%40WayneEK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20the%20CurrentRegion%20property%2C%20you%20may%20try%20something%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-c%22%3E%3CCODE%3EActiveSheet.ListObjects.Add(xlSrcRange%2C%20Range(%22A1%22).CurrentRegion%2C%20%2C%20xlYes).Name%20%3D%20%22Table1%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894240%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Size%20in%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894240%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10827%22%20target%3D%22_blank%22%3E%40Smitty%20Smith%3C%2FA%3E%26nbsp%3BThanks%20so%20much.%20Several%20responded%20with%20the%20same%20info%20and%20it%20worked.%20Very%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894245%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Size%20in%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894245%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThanks%20so%20much.%20Several%20responded%20with%20the%20same%20info%20and%20it%20worked.%20Very%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
WayneEK
Occasional 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
Highlighted

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

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

 

Highlighted

@WayneEK 

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

 

 

 

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

 

 

 

 

 

Highlighted

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

Highlighted

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

Highlighted

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

Related Conversations
Pulling word data into excel
amandamy in Excel on
0 Replies
True of false if any column is non-blank
Ocasio27 in Excel on
0 Replies
Data Tab / Using Stock Data
William_Parrish in Excel on
0 Replies
Transferring long tables to Word?
laonglaan in Excel on
2 Replies
Erro ao abrir a planilha
amauripaulo1978 in Excel on
0 Replies
Problem Determining Specific Dates with Formulas
TraderRef in Excel on
2 Replies