title: VBA - Range.Row.Count
body: <p>I have written a simple code to illustrate my predicament. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Why doesn't k = 1?</p>
  
  <p>Pictures:</p>
  
  <p><img src="https://i.stack.imgur.com/rLYlk.png" alt="The code works"></p>
  
  <p><img src="https://i.stack.imgur.com/MRKCs.png" alt="This is wrong"></p>
  
  <p>EDIT: The workaround that I'm using is the following:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>