selecting all cells even with empty rows...

[GS7CLW]

I am trying to select (with a macro) the entire WS. The code below would work if there were no empty lines. How can I change it so this macro will select the entire page even with 1 or 2 empty rows.

lastNameRow = Range("F" & Rows.Count).End(xlUp).Row
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:L" & lastNameRow), , xlNo).Name = _
        "Table1"
Range("Table1[#All]").Select

IT'S FRIDAY;>))
hope everyone has a GREAT weekend.

 

[charleymax]

I am trying to select (with a macro) the entire WS. The code below would work if there were no empty lines. How can I change it so this macro will select the entire page even with 1 or 2 empty rows.

lastNameRow = Range("F" & Rows.Count).End(xlUp).Row
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:L" & lastNameRow), , xlNo).Name = _
        "Table1"
Range("Table1[#All]").Select

IT'S FRIDAY;>))
hope everyone has a GREAT weekend.

Use
Cells.Select

then you can use Selection.Copy or what ever it is you want to do...

 

[VoG]

Try

ActiveSheet.UsedRange.Select

 

[GS7CLW]

Try

ActiveSheet.UsedRange.Select

This solves the selection problem!!

What do I need to do with:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:L" & lastNameRow), , xlNo).Name = _
        "Table1"
Range("Table1[#All]").Select

I have tried:

[ActiveSheet.ListObjects.Add(xlSrcRange, Range(UsedRange), , xlNo).Name = _
"Table1"
Range("Table1[#All]").Select

 

[GS7CLW]

I got it:

LastColumn = ActiveSheet.UsedRange.Column - 1 + _
             ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.UsedRange.Row - 1 + _
          ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$" & LastRow), , xlNo).Name = "Table1"
Range("Table1[#All]").Select

Thanks EVERYONE!
Have a GREAT weekend.....