VBA - Range.Row.Count

Question

I have written a simple code to illustrate my predicament.

Sub test()     Dim sh As Worksheet     Set sh = ThisWorkbook.Sheets("Sheet1")      Dim k As Long      k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count End Sub 

What happens is this: We count the rows which contain values starting at A1. If the number of rows which contain values is > 1 the code works great. However, if A1 is the only cell which contains any value, k = 1,048,576 which I guess is the maximum number of rows allowed in Excel.

Why doesn't k = 1?

Pictures:

EDIT: The workaround that I'm using is the following:

Sub test()     Dim sh As Worksheet     Set sh = ThisWorkbook.Sheets("Sheet1")      Dim k As Long      k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count     If k = 1048576 Then         k = 1     End If     MsgBox (k) End Sub 

Since k is always equal to 1048576 when the number of rows with values is 1. It just feels a bit silly having to do something like this.

Answer 1

Probably a better solution is work upwards from the bottom:

k=sh.Range("A1048576").end(xlUp).row 

Answer 2

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 - 1 End 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