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

 

[Blade Hunter]

You can do it in one line and remove the need for variables

Range("B5:" & Range("IV5").End(xlToLeft).Address).select

 

[Blade Hunter]

Dim LastCol as Integer

Long, Excel doesn't use Integers, it converts them to Longs so you may as well have it as a long to begin with

 

[Tom Urtis]

I'm trying to select a range from B5: to last row : last column..

Sub Test1()
Dim LastRow As Long, LastColumn As Long
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range("B5:" & Cells(LastRow, LastColumn).Address).Select
End Sub

 

[Blade Hunter]

There is a slight mistake with my previous post, it should be:

Range("B5:" & cells(rows.count,Range("IV5").End(xlToLeft).column).end(xlUp).address).select

 

[Emm]

Yaaaahhh... at last..

LastRow = Range("B" & Rows.Count).End(xlUp).Row
LastCol = Range("B5").End(xlToRight).Column
Range("B5", Cells(LastRow, LastCol)).Select

Thanks all for your help..

Keith

 

[Michael M]

Hi Blade
Ok, so you'll have to help me here....
Whenever I use Long, I get an error.
If I use Integer it works.....Do I have an Incorrect setting somewhere.

 

[Blade Hunter]

Yaaaahhh... at last..

LastRow = Range("B" & Rows.Count).End(xlUp).Row
LastCol = Range("B5").End(xlToRight).Column
Range("B5", Cells(LastRow, LastCol)).Select

Thanks all for your help..

Keith

Be aware that if any column other than B has data lower than column B it will get chopped off and if you have any blank cells in row 5 after column B it will take that as being the end of the data.

 

[Blade Hunter]

Hi Blade
Ok, so you'll have to help me here....
Whenever I use Long, I get an error.
If I use Integer it works.....Do I have an Incorrect setting somewhere.

So much code all over this thread .

Can you post the code that is erroring?

Cheers

Dan

 

[Tom Urtis]

Yaaaahhh... at last..

LastRow = Range("B" & Rows.Count).End(xlUp).Row
LastCol = Range("B5").End(xlToRight).Column
Range("B5", Cells(LastRow, LastCol)).Select

Thanks all for your help..

Keith

Why do you think that would work for you? It will fail if some other column to the right of column B has a value in a cell below the row holding the last value in column B, and / or if a column has a value in a cell to the right of the column holding the last value in row 5. My code will take that into consideration and the code you are happy about will not.

 

[Michael M]

Hi Blade
Mate, It's not an issue for this thread.
I'll dig it up and post at a later time.
I have also changed the way I find the last col to the one I used here.
I used something different in the past, so that also may have caused the problem.

Cheers mate.