Count number of non-blank cells in a range

Hi welchs101, how you display this is up to you but the following procedure will count everything (even the headers).

Sub NonBlanks()
Dim RngToSrch As Range, cl As Range, RngCnt As Long
Set RngToSrch = Sheets(1).Range("C5").CurrentRegion
RngCnt = 0
For Each cl In RngToSrch
    If cl.Value <> "" Then RngCnt = RngCnt + 1
Next cl
MsgBox "The count is: " & RngCnt
End Sub

FYI - you can use WorksheetFunction.CountA() to count the number of non-empty cells in a given range.

Originally Posted by Colin Legg

FYI - you can use WorksheetFunction.CountA() to count the number of non-empty cells in a given range.

Do you apply that to the whole CurrentRegion at once?

If you want a count of both empty and non empty cells, try:

Sub NonBlanks()
Dim RngToSrch As Range, cl As Range, RngCnt As Long, EmptyCnt As Long
Set RngToSrch = Sheets(1).Range("C5").CurrentRegion
RngCnt = 0
EmptyCnt = 0
For Each cl In RngToSrch
    If cl.Value <> "" Then
        RngCnt = RngCnt + 1
    Else: EmptyCnt = EmptyCnt + 1
    End If
Next cl
MsgBox "The sum of cells with values are: " & RngCnt & vbNewLine _
    & "The sum of cells with no values are: " & EmptyCnt
End Sub

Wow that shortens the code!

Sub NonBlanks()
Dim RngToSrch As Range, cl As Range, RngCnt As Long
Set RngToSrch = Sheets(1).Range("C5").CurrentRegion
RngCnt = 0
Debug.Print WorksheetFunction.CountA(RngToSrch)
End Sub

Nice one Colin!

Hi welchs101, The following code works base on you selecting a whole row and then running the code, which is also based on the workbook you provided where the first fill column is at C.

Sub NonBlanks2()
    Dim rNum As Long, rAddress As String
    Dim LastCol As Long
    rAddress = Selection.Address
    rNum = Range(rAddress).Row
    LastCol = Cells(5, Columns.Count).End(xlToLeft).Column
    Debug.Print WorksheetFunction.CountA(Range(Cells(rNum, 3), Cells(rNum, LastCol)))
End Sub