[vba] VBA - Range.Row.Count

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.

This works for me especially in pivots table filtering when I want the count of cells with data on a filtered column. Reduce k accordingly (k - 1) if you have a header row for filtering:

k = Sheets("Sheet1").Range("$A:$A").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Count

Have you tried :-

Sub test()

    k = Cells(Rows.Count, "A").End(xlUp).Row

    MsgBox (k)

End Sub

The /only/ catch is that if there is no data it still returns 1.

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

That is nice question :)

When you have situation with 1 cell (A1), it is important to identify if second declared cell is not empty (sh.Range("A1").End(xlDown)). If it is true it means your range got out of control :) Look at code below:

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Arkusz1")

Dim k As Long

If IsEmpty(sh.Range("A1").End(xlDown)) = True Then
    k = 1

Else
    k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count

End If

In case anyone looks at this again, you can use this:

Sub test()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim k As Long
    k = sh.Range("A1", sh.Range("A1").End(xlDown).End(xlDown).End(xlUp)).Rows.Count
End Sub

You could also use the "Last" function from Ron de Bruin (http://www.rondebruin.nl/win/s9/win005.htm), it worked perfectly for me and also gives back the last column and cell if you want. To get the last row use it like

lastRow = Last(1,yourRange)

I found this quite handy.

Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
    Dim lrw As Long
    Dim lcol As Long

    Select Case choice

    Case 1:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(What:="*", _
                       After:=rng.Cells(1), _
                       Lookat:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0

        On Error Resume Next
        lcol = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

        On Error Resume Next
        Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
        If Err.Number > 0 Then
            Last = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0

    End Select
End Function

k = sh.Range("A2", sh.Range("A1").End(xlDown)).Rows.Count

or

k = sh.Range("A2", sh.Range("A1").End(xlDown)).Cells.Count

or

k = sh.Range("A2", sh.Range("A1").End(xlDown)).Count

The best solution is to use

Cells(Rows.Count, 1).End(xlUp).Row

since it counts the number of cells until it finds the last one written.

Unlike

Range("A1", sh.Range("A1").End(xlDown)).Rows.Count

what it does is select an "from-to" range and display the row number of the last one busy.
A range implies two minimum values, so ... meanwhile A1 has a value of the range continues to count to the limit (1048576) then it is shown.

Sub test()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(1)
Dim k As Long

    k = Cells(Rows.Count, 1).End(xlUp).Row
    MsgBox k

    k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
    MsgBox k

End Sub

CountRows = ThisWorkbook.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count