Making a CountRows function in Excel

Question

I am trying to make a simple countRows function that will count the number of cells I have in a dynamic range. Basically if I have values in cells, say B2:B500, the count would return 499. However next time around values are in cell B2:B501, the count would return 500. But you wouldn't have to do anything to the cell in which you typed in the formula.

I thought if I reference the cell as a Variant, then any value could be accepted. Then find the Address of that cell and return the Count of a Range. But I get a #Value error.

Public Function countRows(startRange As Variant)

    Dim rng As Range
    Set rng = startRange.Address


    If IsEmpty(Range(rng, rng.End(xlDown))) = True Then
        countRows = 1
    Else
        countRows = Range(rng, rng.End(xlDown)).Rows.Count
    End If

End Function

Answer 1

This is the code I have used for many years successfully under many different worksheets. It handles many cells, singular cells or empty cells.

Public Function CountRows(ByRef r As Range) As Long
    If IsEmpty(r) Then
        CountRows = 0
    ElseIf IsEmpty(r.Offset(1, 0)) Then
        CountRows = 1
    Else
        CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.count
    End If
End Function

Public Function CountCols(ByRef r As Range) As Long
    If IsEmpty(r) Then
        CountCols = 0
    ElseIf IsEmpty(r.Offset(0, 1)) Then
        CountCols = 1
    Else
        CountCols = r.Worksheet.Range(r, r.End(xlToRight)).Columns.count
    End If
End Function