VBA Find last row in last column

[Toffee]

Hi All,
I'm loosing my will to live, I need to find the last row in the last column:

LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

LastRow = Cells.Find(What:="*", After:=[A],_
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

I want [A] in the LastRow to be LastColumn

Help please
many Thanks in advance

 

[Abdul Matheen]

Hi,


Try the below code to get the last row in last column.


Sub LCLR()
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
lastcolumn = ActiveCell.Column
lastrow = ActiveCell.Row
MsgBox "Column - " & lastcolumn & ": Row - " & lastrow

End Sub

Regards
Abdul Matheen

 

[Luke M]

Abdul's would find the last row regardless of column. I think a small tweak could make it like this:

Sub LCLR()
Dim LastColumn As Long
Dim LastRow As Long
LastColumn = Cells.Find(what:="*", after:=[A1], _
 searchorder:=xlByColumns, _
 searchdirection:=xlPrevious).Column
 
LastRow = Cells(1, LastColumn).EntireColumn.Find("*", after:=Cells(1, LastColumn), _
  searchorder:=xlByRows, searchdirection:=xlPrevious).Row
 MsgBox "Column - " & LastColumn & ": Row - " & LastRow
 End Sub

This will give the last column used, and the last row within that column.

 

[Toffee]

Hi,
Abdul - thank you very much for you time and suggestion, but It's Luke M's answer that i was looking for, i need this to create a loop to copy and paste range of 3 columns and 5 rows working from the last column and i was really stuck.

Luke M - sheer perfection, Many thanks

Kind Regards
Toffee