Count rows in VBA

[Joy Liao]

Hello guys!

I'd like to count how many rows with contents.
But I tried lots of methods, I got "the number of the bottom row".

Ex.
My data are like "A1,A3,A99", and I wanna get "3 rows", but turned into "99 rows" always.

 

[rlv01]

There are many ways to do that. Here are two:

Sub CountRowsWithContents()

    Dim CellRange As Range
    Dim Method1 As Long, Method2 As Long

    With ActiveSheet
        Set CellRange = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
    End With

    Method1 = Application.WorksheetFunction.CountA(CellRange)

    Method2 = CellRange.SpecialCells(xlCellTypeConstants).Count

    MsgBox "Method 1 = " & Method1 & vbCr & "Method 2 = " & Method2
End Sub