Excel VBA: Get Last Cell Containing Data within Selected Range

using Find like below is useful as it

• can find the last (or first) cell in a 2D range immediately


• testing for Nothing identifies a blank range


• will work on a range that may not be contiguous (ie a SpecialCells range)

change "YourSheet" to the name of the sheet you are searching

Sub Method2()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Sheets("YourSheet")
    Set rng1 = ws.Columns("A:B").Find("*", ws.[a1], xlValues, , xlByRows, xlPrevious)
    If Not rng1 Is Nothing Then

        MsgBox "last cell is " & rng1.Address(0, 0)
    Else
        MsgBox ws.Name & " columns A:B are empty", vbCritical
    End If
End Sub