excel-vba Last cell in Range.CurrentRegion


Range.CurrentRegion is a rectangular range area surrounded by empty cells. Blank cells with formulas such as ="" or ' are not considered blank (even by the ISBLANK Excel function).

Dim rng As Range, lastCell As Range
Set rng = Range("C3").CurrentRegion       ' or Set rng = Sheet1.UsedRange.CurrentRegion
Set lastCell = rng(rng.Rows.Count, rng.Columns.Count)