LastRow VBA

[animas]

I need to use a function like
=LastRow(B2:B100)
will return B25 (B26 is last non empty cell within range B2:B100)

=LastRow(B:B)
will return B207 (B207 is last non empty cell within range B:B)

I was trying below which is not working

Public Function LastRow(iRange As Range)
    LastRow = Range(iRange).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Address
End Function

Any solution?

 

[Airfix9]

No need for a function, I handle it in the procedure

dim lr as long
lr = ThisWorkbook.Sheets("Sheet1").Range("B1").End(xlDown).Row

That should do you, edit to suit your workbook.

 

[JoeMo]

If you want to return the address of the last cell in a range try this:

Function lastCell(iRange As Range) As String
Dim LastRow As Long, LastCol As Long
' Error-handling is here in case there is not any
' data in the range.
On Error Resume Next
With iRange
' Find the last row
    LastRow = .Cells.Find(what:="*", _
      searchdirection:=xlPrevious, _
      searchorder:=xlByRows).Row
  ' Find the last column
    LastCol = .Cells.Find(what:="*", _
      searchdirection:=xlPrevious, _
      searchorder:=xlByColumns).Column
    If Err.Number <> 0 Then 'worksheet is empty
       MsgBox ("Worksheet " & ws.Name & " is empty (all cells are blank).")
        lastCell = .Cells(1, 1).Address
    Else
       lastCell = .Cells(LastRow, LastCol).Address
    End If
End With
End Function

 

[lozzablake]

Something like this should work. Function returns an error value if there is more one column in the range. It also checks in case the range selected is in a block of data in which case the last row should be returned.

Function LastRow(myRange As Range) As Long
    
    With myRange
        If .Columns.Count > 1 Then
            'more than one column
            LastRow = 99999999
        ElseIf Cells(.Rows.Count + .Row - 1, .Column) <> "" Then
            'last row contains data
            LastRow = .Rows.Count + .Row - 1
        Else
            LastRow = Cells(.Rows.Count + .Row - 1, .Column).End(xlUp).Row
        End If
    End With

End Function

 

[animas]

I was looking for returning last cell address within a range. JoeMo code is giving wrong addresses.

 

[JoeMo]

I was looking for returning last cell address within a range. JoeMo code is giving wrong addresses.

Can you post an example where the wrong address is returned?