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 answers

  1.
    xjl Posted messages 232 Status Member 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
    1. Raph
       
      Thank you Xjl, I found a shorter one ;)

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

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