Use VBA to Count Non Blank Cells in a Column

michaeldh

Board Regular
Joined
Jun 11, 2002
Messages
201
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.

Michael.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
Code I am using for deleting empty columns (except for a header)
Can be adapted for purpose...

Code:
    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
        If lRealLastRow - WorksheetFunction.CountBlank(Intersect(Columns(i), ActiveSheet.UsedRange)) <= 1 Then _
            Columns(i).EntireColumn.Delete
    Next i
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,530
Messages
6,172,843
Members
452,484
Latest member
vmexwindy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top