The count function is not counting certain cells, it brings it up as zero vba-CodePudding

The count function is not working as intended. It brings up the selected count as 0, even though there is data in those cells. I tried with different cells and it works for some but not others - some of the cells have drop down information, but not all.

Sub AssignCount()
   Dim result As Integer
'Assign the variable
   result = WorksheetFunction.Count(Range("C11:C18"))
'Show the result
   MsgBox "The number of cells populated with values is " & result
   
End Sub

CodePudding user response：

Best to be explicit about your range object. Also, depending on what you want to count, try to switch to COUNTA() function to count numeric & string values:

Sub AssignCount()
    Dim sht As Worksheet: Set sht = ThisWorkbook.Worksheets("resourcing")
    Dim lr As Long, result As Long: lr = sht.Cells(sht.Rows.Count, 2).End(xlUp).Row
    Dim rng As Range: Set rng = sht.Range("B2:B" & lr)
    
    result = WorksheetFunction.CountA(rng)
    MsgBox "The number of cells populated with values is " & result
End Sub

CodePudding user response：

Count : Only count the numeric values

It's better to use CountA instead which works for both number & categorical data