Count function

[BryceVBA]

Hello, I need to count how many cells have entries in column A. I have the following code so far, but the variable is returning 17 instead of the 2 entries that actually exist. Does anyone know a more reliable count function that wont include blanks?

Dim CountVar As Long
    CountVar = Application.CountA(Columns("A"))

[YasserKhalil]

May be you have some cells with space in them " " so try this

Sub Test()
    Dim countVar As Long
    countVar = Application.CountA(Columns(1)) - Application.CountIf(Columns(1), " ")
    MsgBox countVar
End Sub

[BryceVBA]

May be you have some cells with space in them " " so try this

Sub Test()
    Dim countVar As Long
    countVar = Application.CountA(Columns(1)) - Application.CountIf(Columns(1), " ")
    MsgBox countVar
End Sub

This code gave me 500 returns instead of 17. haha

[gondal]

try this

Sub Test()
    Dim lngCount As Long
lngCount = Application.WorksheetFunction.CountA(Columns(1))
MsgBox lngCount
End Sub

[BryceVBA]

try this

Sub Test()
    Dim lngCount As Long
lngCount = Application.WorksheetFunction.CountA(Columns(1))
MsgBox lngCount
End Sub

Same with this code. 500 returns

[gondal]

can you upload the sheet..??

[BryceVBA]

So instead of looking in the whole column, I limited the search to A1:A10, since this variable shouldn't have too many entries.

CountVar = WorksheetFunction.CountA(Range("A1:A10"))

Also I owe you both an apology. I was originally using your code with a report that the code isn't compatible with yet, so the results were invalid. I tried both of your codes again with compatible reports and they both returned 17 still.

[MarvinP]

Hi Bryce,

You could also have a formula in a cell that returns a blank. This would look like a blank cell but be counted in the CountA function.

https://www.thoughtco.com/counting-a...f-data-3123571

[Phuocam]

 
countVar = Application.CountIf(Columns("A"), "?*")