How to get adress of last non empty cell in a excel using MACROS(VBA)

3
votes

I want to get the cell address of the last non empty cell within an excel sheet. Basically I want the row and column number/name of the last non empty cell. I have found few ansers to find out the value in the last non empty cell but I need cell adress not contents.

1
vbaexcel
What is the definition of "last"? – GreenAsJade
Suppose there is a table from A1 to D4 and below that there is no cell in excel with a value below that, then D4 will be last non empty cell. If there is some value at Z44 below the table, then the last non empty cell will be Z44. I want to get this Z44 using macros – Nitish Bangad
From what you say, you already know where the cell is, so just instead of range().value, use range().address. and next time show the code you already have – Patrick Lepelletier

1 Answers

3
votes

For data like:

xsdew

Most people would like to find the Blue cell:

Sub FindBlue()
    Dim rng As Range
    Set rng = Cells.Find(What:="*", After:=Cells(1), SearchDirection:=xlPrevious, searchorder:=xlByRows)
    MsgBox rng.Address(0, 0)
End Sub

If you want the Yellow cell then:

Sub FindYellow()
    Dim rng As Range
    Set rng = Cells.Find(What:="*", After:=Cells(1), SearchDirection:=xlPrevious, searchorder:=xlByColumns)
    MsgBox rng.Address(0, 0)
End Sub