View original (French)

[VBA Excel] count non-empty cells in a column

Raph -  
 ensatien -
Hello,

How to know the number of non-empty cells in column A for example in sheet1?
It's easy with the function NB.Si, but in VBA?

Does anyone have an idea?

Thank you

Raph
Configuration: Windows XP Internet Explorer 6.0

2 réponses

Réponse 1 / 2
xjl Posted messages 232 Status Membre 183
 
Hello,

You can do this:

Sub counting()
n = 0
For Each Cell In Sheets(1).Columns(1).Cells
If IsEmpty(Cell) Then n = n + 1
Next Cell
MsgBox n

End Sub
13
Raph
 
Thank you Xjl, I found a shorter one ;)

nbcells = Application.WorksheetFunction.CountA(Sheet1.Range("$A:$A"))
0
hammougua
 
Ab44 July 25, 2009 at 1:15 PM
and what would you do for column B?
and how do you declare nbcells?
0
Gotts
 
Dim nbcells As Integer

and for B, you just need to replace the A's with B's...
0