How to get last row through VBA programming

[Mugdha]

Hi every one.

Usually I download a report in excel, apply filter and delete some rows.
This changes my "last cell" reference, which has totals. Could you please
tell me how do I get a row index/number. I want to use it it while writting a
Macro.

Thanks,
Mugdha.

 

[Scott Ketelaar]

You can use this function

Function GetCellAddress(Cell As Range, Optional Absolute As Boolean = False)
'Function written 2009 by Scott Ketelaar
If Absolute = True Then
GetCellAddress = Cell.Address
Else
GetCellAddress = Replace(Selection.Address, "$", "")
End If
End Function

like this.

GetCellAddress ("A1") produces A1
GetCellAddress("A1",true) produces "$A$1"
GetCellAddress(selection,false) produces the selection address

If you want only the row or column, you can use the .Row and . Column
respectivly.

For Example:

Msgbox Range("A1").Row Produces 1
Msgbox Range("A1").Column Produces 1 (Not a column letter, a column number)
Msgbox Selection.Row produces the selection row

Hope that helps

--Scott

 

[Scott Ketelaar]

Oops, Just realized i made a slight mistake.
this line :
GetCellAddress = Replace(Selection.Address, "$", "")
should be this:
GetCellAddress = Replace(cell.Address, "$", "")


-Scott