VBA - Dynamic table size

[carlito2002wgn]

Hi,

I recorded a macro, and everything is working perfectly, except one thing! I have several sheets in a workbook that have data in columns A:P. The number of rows with data will vary each time that I run this macro. So, I might run it today with data in Sheet1 from A1:P100, but tomorrow Sheet1 might have data from A1:P200. How can I solve this problem?

This is a sample of what I have right now for three sheets from recording the macro:

Sheets("Albumin").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$100"), , xlYes).Name = _
"Table9"
Range("Table9[#All]").Select
Sheets("ALP").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$100"), , xlYes).Name = _
"Table10"
Range("Table10[#All]").Select
Sheets("ALT").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$100"), , xlYes).Name = _
"Table11"
Range("Table11[#All]").Select


Is the solution to change ie. Range("$A$1:$P$100") to Range("$A$A:$P$P") ?

Thank you for any help!!

Carlos

 

[CalcSux78]

Carlos,

You might try using CurrentRegion to select your table range.

With Sheets("Albumin")
    .ListObjects.Add(xlSrcRange, .[A1].CurrentRegion, , xlYes).Name = "Table9"
End With
With Sheets("ALP")
    .ListObjects.Add(xlSrcRange, .[A1].CurrentRegion, , xlYes).Name = "Table10"
End With
With Sheets("ALT")
    .ListObjects.Add(xlSrcRange, .[A1].CurrentRegion, , xlYes).Name = "Table11"
End With