Use VBA to Count Non Blank Cells in a Column

Michael - try this:

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

Or 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

On searching for an answer to my problem, this is exactley what i needed.
is there a way to exclude the first row (header) from this count?

Also can i then minus where coulmn c = Y

i.e if i had 400 entries(399 minus header) in column A and column be had 50 Y how could i return my count as 349? (am i asking too much)

this would be great.

DonkeyOte said:

Michael - try this:

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

Click to expand...

Excellent... It really helped me.. thnks DonkeyOte

you rock

Hi guys,

I came across this thread after googling something similar to the title.

here is my code:

OSC = Range("A2:A6").Cells.SpecialCells(xlCellTypeConstants).Count
On Error Resume Next

ODC = Range("C2:C6").Cells.SpecialCells(xlCellTypeConstants).Count
On Error Resume Next

ULC = Range("E2:E6").Cells.SpecialCells(xlCellTypeConstants).Count
On Error Resume Next

If OSC > 1 Then
Selection.Sort Key1:=Range("B2:B6"), Order1:=xlDescending, Header:=xlGuess
End If

If ODC > 1 Then
Selection.Sort Key1:=Range("D2:D6"), Order1:=xlDescending, Header:=xlGuess
End If

If ULC > 1 Then
Selection.Sort Key1:=Range("F2:F6"), Order1:=xlDescending, Header:=xlGuess
End If

any idea why it is doing the following:

A B C D E F
1 OS mins OD mins UL mins
2 shift change 11.88 Inspection 99.17
3 Wait for trucks 18.00 AF alarm 62.12 restart 37
4 Hosing chutes 14.67
5 Breaking over 14.50

The code seems to be putting the ones listed in column D into descending order but not column B and with column F it's skipping F2 and putting it straight in to F3.

what i'm trying to do: I'm trying to sort the downtimes in to descending order. The maximum that each column will have is 5 downtimes (ie. A2 to A6 etc). Some columns will have 0 and some will have 1. When a column has zero the .count function gives me an error.

Any help is greatly appreciated.

well, the formatting stuffed up the table I put in.

Pretty much it's doing the descending order for some columns but not others and i'm not sure why

I was looking for this info and wanted to share the info that worked for me to achieve above task of counting non blank cells in any column.


m_Count = 20000
m_Count_column = 4
s_name="tab name"
With Sheets(s_name)
.Cells(m_Count, 1) = "Number of Non blanks cells:"
.Cells(m_Count, m_Count_column).NumberFormat = "General"
.Cells(m_Count, m_Count_column).FormulaR1C1 = "=COUNTA('" & s_name & "'!C2)-1"
Count = .Cells(m_Count, m_Count_column)
End With

Above code will store the count in Cells(m_Count, m_Count_column) of s_name temporarily and later its stored in a variable Count. Count can be used later on.

"=COUNTA('" & s_name & "'!C2)-1" is the formula used to count the non blanks cells in the column 2 of tab s_name of worksheetsheet xyz. C2 means Column 2.

Regards,
Adithya

Code I am using for deleting empty columns (except for a header)
Can be adapted for purpose...

This should do the job....it's a mash-up of the comments above.

Sub countDataByColumns()
Dim lRealLastRow, lRealLastCol As Long

lRealLastRow = Cells.Find("*", Range("A1"), xlValues, , xlByRows, xlPrevious).Row
lRealLastCol = Cells.Find("*", Range("A1"), xlValues, , xlByColumns, xlPrevious).Column

For i = 1 To lRealLastCol
Set Rng = Intersect(Columns(i), 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 " & Columns(i).Column & " is " & n
Next i
End Sub