Select last row last col

[Emm]

Hi All,

I'm trying to select a range from B5: to last row : last column..
I can do it from B5 to a set column & last row...
But cant seem to get the varible on both row & col...

I am using this at present, but finding it misses if something down low in list with gaps:

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

am trying this ...

LastRow = Range("B65536").End(xlUp).Row
LastCol = Range("IV5").End(xlLeft).Column
Range("B5" & LastCol & LastRow).Select
but has errors...

all help appreciated

thanks,

Keith

 

[shg]

Range("B5", Cells(LastRow, LastCol)).Select

 

[hiker95]

Emm,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub SelectUsed()
' hiker95, 09/30/2010, ME498707
Dim LUR As Long, LUC As Long
Application.Volatile
LUR = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LUC = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
ActiveSheet.Range(Cells(5, 2), Cells(LUR, LUC)).Select
End Sub

Then run the "SelectUsed" macro.

 

[Emm]

Thanks Guys,

Where my error is coming in is with this line:

LastCol = Range("IV5").End(xlLeft).Column

Hiker, just about to try yours..

Thanks,

Keith

 

[hiker95]

Emm,


The 5 stands for row 5.

LastCol = Cells([B]5[/B], Columns.Count).End(xlToLeft).Column

 

[Emm]

Thanks Hiker ..
But still getting error on last line..
Application defined or Object defined error...

I have:

LastRow = Range("B65536").End(xlUp).Row
LastCol = Cells(5, Columns.Count).End(xlToLeft).Column
Range("B5", Cells(LastRow, LastCol)).Select

Nearly there

Keith

 

[Emm]

ok .. got it ..

Thanks all..

ActiveSheet.Range("b5", ActiveSheet.Cells(LastRow, LastCol)).Select

Keith

 

[Emm]

nope .. didn't work ???

 

[Emm]

OK ..

I can find LastRow

I can select the Range from B5 to last row & col

But my selection of last col is not working ...

I have data from col B:S (S changes) then a gap and other data fro aa:ad

This works fine.... finds the correct column
but the varible value is the value of the cell, not the address???

LastCol = Selection.End(xlToRight)

what should my Dim be declared as???

Keith

 

[Michael M]

Dim LastCol as Integer


LastRow should also be

LastRow = Range("B" & Rows.Count).End(xlUp).Row

Using "B65536" will work fine but may cause issues when you try to run it in 07, as there are more than 65536 rows.