VBA - Range.Row.Count
You should use UsedRange
instead like so:
Sub test() Dim sh As Worksheet Dim rn As Range Set sh = ThisWorkbook.Sheets("Sheet1") Dim k As Long Set rn = sh.UsedRange k = rn.Rows.Count + rn.Row - 1End Sub
The + rn.Row - 1
part is because the UsedRange only starts at the first row and column used, so if you have something in row 3 to 10, but rows 1 and 2 is empty, rn.Rows.Count
would be 8