Excel – Use VBA to Count Non Blank Cells in a Column

--> (Word)

--> (PDF)

--> (Epub)

SCENARIO

Hi Guys,
Could you please be so kind as to provide me with some code that can count the number of non-blank cells in a selected column.
Thanks for your help.

SOLUTION

If the column consists of constants, formulas and blanks:

Sub Count_NonBlank_Cells()
        Dim col As Integer, rng As Range, n#, b#
  
col = Selection.Column
  
If Application.WorksheetFunction.CountA(Columns(col)) = 0 Then
  
MsgBox "You have selected a blank column"
  
n = 0
  
Else
  
Set rng = Intersect(Columns(col), ActiveSheet.UsedRange)
  
On Error Resume Next
  
b = rng.Cells.SpecialCells(xlCellTypeBlanks).Count
  
n = rng.Cells.Count - b
  
On Error GoTo 0
  
MsgBox "The number of non-blank cells in column " & col & " is " & n
  
End If
  
End Sub

SOLUTION (2)

var0 = Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count