CODEWITHSUNDEEP
vbaexcelcount
ajs
ajs

Reputation: 335

how to count of the number of filled cells in a column in an excel sheet

So I'm currently trying to get a count of the number of filled cells in a column in an excel sheet, but I'm unsure as to how to do that. I've seen things like:

Range("A1").End(xlDown).Row

but that doesn't give an actual count. Does anyone know how to get an integer valued count? Are there any methods built into excel for that purpose?

Thanks in advance.

Upvotes: 2

Views: 43043

Answers (2)

Qbik
Qbik

Reputation: 6157

You can also loop throught the cells range, I wonder which method is faster - this or using Excel's CountA (below would go into almost infinite loop if r range is the whole column):

Sub test()

Dim r As Range
Dim c As Long

Set r = Range("A1").End(xlDown).Row

For Each x In r
   If Not r = "" Then
      c = c + 1
   End If
Next r

MsgBox r

End Sub

Upvotes: 0

bilbo_strikes_back
bilbo_strikes_back

Reputation: 591

The counta function could work. 

'If the data is in column A
Dim lngCount as Long
lngCount = Application.WorksheetFunction.CountA(Columns(1))
msgbox lngCount

Upvotes: 4

Related Questions