Free Microsoft Excel 2013 Quick Reference

Use VBA to Count Non Blank Cells in a Column

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.


Post your answer or comment

comments powered by Disqus

I am trying to write a function for a cell that counts non-contiguous cells in a column and then sums them together. The issue is this cell will be copied to multiple worksheets and will have to re-count all the cells when it is copied.

This is my first time writing a function.

Thanks in advance

In previous versions of Excel, when adding an Auto Filter to a column, a user
was able to select "non-blanks" from the pull down menu which would cause all
cells with no values or text within the same column to be hidden. In Excel
2007, the "non-blanks" option has been removed. How does one collapse all the
non-blank cells within a column using the filter in Excel 2007?

how can I COUNT only up to the first blank cell in a column?

I am trying to get the value of the last non blank cell in a column on different a worksheet. The example below works perfect if everything is on one worksheet, but I am needing this to work with the data being on Sheet2 and the results of the formula to appear in cell B1 of Sheet1 since the database on Sheet2 will be rather large. Does anyone anyone know how to make this work?

10 AAA
11 CCC
12 AAA
13 DDD
14 DDD

Enter the following formula in cell B1: =OFFSET(A1,COUNTA(A:A)-1,0)

The result: DDD

I am trying to create a cell at the top of a worksheet that will give me the
last non-blank cell in a column. The column is a list of dates.

Any help is much appreciated.

Bob Weeden

Help me with this please: I need a macro to find first blank cell in a column cut the value from left adjacent column and paste in that first blank cell and continue routine until last blank cell and stop


I have the number of a column in a variable “ab”
How do I write the VB statement to have the CountA function count the non-blank cells in the Column?

ActiveCell = "=COUNTA(???:???)"

Or is there another way to do it with VB?
Thanks for any help you can give me.


An example of my data set looks like this

colA B C D E F G
year (annual tree ring width....each column is an individual tree)
1780 0 0 0 1 0 0
1781 0 2 0 1 2 0
1782 3 4 0 5 3 4
1783 3 1 2 4 2 4
1784 3 3 3 3 3 3
1785 3 4 3 1 2 1
1786 5 2 1 3 2 1

What I am trying to do is get the average of the first 5 non-blank data values in each column.

However, some columns have blank cells (0) before the actual data values start. How can I get an average of the first 5 non blank cells in each column?

(I want to know the average tree ring width over the first 5 years of growth for each tree)

Thanks for any help...have looked all over for this answer! I have at least 800 columns of data and def could use a short cut!

I am trying to create a macro that will copy the last non-blank cell in a
running total column that appears on a number of worksheets to a summary
worksheet in the same workbook. Any suggestions?


I need to select the blank cells in a column to place a formula in them. The sheet is protected so I cannot use specialcells. Can anybody suggest another way to acheive this without using a loop.

Hopefully a simple question - how do you find the last non blank cell in
a column ? The trick is that the column may contain blank cells along
the way before reaching the last non blank cell.

Andrew Bourke


I've got a macro that will find solutions to a math problem. The same
macro runs several for/next loops searching for numbers that match
criteria for solutions. Most of these loops find multiple solutions.
How do I make the numbers go to the next blank cell in a column?

Say for example the macro finds 25 and 40 are solutions. In the macro,
the solution is temporarily held as variable x. I need 25 to be in
say, A1 and 40 to be in A2, etc...

I seem to be 'search challenged' today - I'm sure this is easy. How does one count the number of non-blank cells in a range using VBA?

I am trying to create VBA code to count the number of cells within a range that have either text or numbers entered in them. This needs to work on a row by row basis, i.e. the same code applied to row 6, 7, 8 onwards.

I have 12 cells in a row range that are being written into the spreadsheet using another code.

The activecell is set as a cell in column B

The 12 cells (labelled 'Deployments') are found in columns AL:AW and are being written using the codes


How do I create a countif code that is able to reference these values?

Can post a sample worksheet if it helps. Alternatively, the worksheet can be found under a previous thread


I'm working with 2 columns out of an Excel spreadsheet. First column "B" is
list of names (some cells may be blank) and the second column "F" contains
years (2001, 2002, etc.). I'd like to count non blank cells in "B" which
meet the criteria (2005) in the years "F" column.


I am trying to produce a formula to count the number of non-blank cells to the right of the cell with the formula in it. I want the formula to return a '1' if there are 3 or more non-blank cells or a '0' if there are less than 3.

At the moment my formula is (example in this case for row 3):


This is not working though and I don't know why. On rows where I have 3 or more non-blank cells it is still returning a '0' everytime.

Can anyone help??



I have a file........see enclosed.

I want to count the number of non-blank cells in a range from A to Z along a given row.

Then once the loop is done i want it to tell me the row which had the most non-empty cells but its not working.

I'm working with 2 columns out of an Excel spreadsheet. First column "B" is
list of names (some cells may be blank) and the second column "F" contains
years (2001, 2002, etc.). I'd like to count non blank cells in "B" which
meet the criteria (2005) in the years "F" column.

I am trying to post the results of one cell to the next blank cell in a different row. For example; I need the results in S3 to post to the next blank cell in row P. I tried the worksheet_change code below but that will only work if I make a manual entry in S3 and does not work if I use a formula in S3 to perform the calculation. My formula in S3 is =MAX(L5:L505). When I hit F9 to recalculate I need the results of S3 to post in row P each time. Perhaps there is a formula that I can use as opposed to VBA.

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Address  "$S$3" Then Exit Sub 
    Application.EnableEvents = False 
    Range("P65536").End(xlUp).Offset(1, 0).Value = Target.Value 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I want to find the Row Number, not necessarily the cell value, of the last non-blank entry in a Column.

If the address of this row is found, then that could also be useful. I believe there are some simple Excel functions to do this, maybe involving the X1Up feature. I've searched the threads and haven't found a clear answer this.

Below is code that I custom wrote, but its long and tiresome to use.

 'Knowing this last row is useful for telling later
 'looping operations which row to stop on.
 'The logic of this sub is that it will look down a column
 'that you specify till it finds the first blank cell entry
Dim axyz As String 'just a variable to hold the value of what's in the cell
Dim nthRow As Long 'a counting variable for Rows could get up to 65536
Dim FirstRowofData As Long 'could be up to row # 65536
Dim LastRowofData As Long 'could be up to row # 65536
Dim ColumntoCheck As Integer 'will always be less than 256 and a whole number
Dim NumberofDataRows As Long 'coudl be up to 65536
axyz = "something" ' axyz can't be empty in memory when starting because
 ' the Do Loop is looking for the first time it is empty below
FirstRowofData = 4 ' user tells macro row where data begins on
 ' say if = 4, the first three rows where titles, words, etc.
ColumntoCheck = 2 ' user tells it which column to look at to determine length
nthRow = FirstRowofData ' just passing value from above, just makes code clearer below
 'Until axyz is empty, it will keep checking down the column
 'Its necessary to tell the loop to increment by one since
 'a Do-Loop won't increment by itself like a For-To-Next loop
Do Until axyz = "" 
    axyz = Columns(ColumntoCheck).Rows(nthRow).Value 
    nthRow = nthRow + 1 
LastRowofData = nthRow - 2 
 ' Above is a numeric correction based on the logic of this loop
 ' The loop was = 1 before it started counting, then
 ' when it reached one past the end of filled rows
 ' it was actually at + 2 so the - 2 corrects for this.
NumberofDataRows = LastRowofData - FirstRowofData 
MsgBox "Column = " & ColumntoCheck & Chr(10) _ 
& "Data starts on row = " & FirstRowofData & Chr(10) _ 
& "Data ends on row = " & LastRowofData & Chr(10) _ 
& "Number of Data Rows = " & NumberofDataRows, vbOKOnly, "OUTPUT" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to count the number of non-blank cells in a column. However, all the "blank cells" have embedded formulas.

I have tried:

Any suggestions?


I am trying to count the Maximum Number of Blank Cells between Non Blank
Cells in a Range.
Range A1:a13
a1 - 2
a3 - blank
a4 - blank
a5 - 24
a6 - blank
a7 - 7
a8 - 51
a9 - blank
a10 - blank
a11 blank
a12 - blank
a13 - 6
The answer is 4. Cells a9 to a12.
Any help appreciated.


In MS Excel 2007, I'm trying to use a function to search for (and display) a
specific text string that resides within a cell, providing that it is the
last non blank cell in a range of cells across a row.

e.g. If searching row 2:2 (or a section of this row) for the last non blank
cell, I want to then search within that last cell to locate the word "pass",
where the same cell may also contain any combination of text/number values.
If "pass" exists within the cell, I want to return a the same text value of
"pass". Note that “pass” may exist across various cells in the range.

-- I've managed to successfully return the entire contents that reside
within the last non-blank cell (with the following formula):

=IF(ISNA(LOOKUP(2,1/('QA Results'!$D2:$CY2""),'QA
Results'!$D2:$CY2)),"",LOOKUP(2,1/('QA Results'!$D2:$CY2""),'QA

-- I've also achieved similar results when searching the cell range for the
last known cell that successfully contains the text “pass” (this is not
necessarily the last non blank cell in the range). Again the entire cell
content is returned.

Results'!D2:CY2),'QA Results'!$D$2:$CY$2))),"",LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))

Any assistance would be appreciated.

Forgive me for these trivial questions, but I cant find the answer using the documentation.

I want a formula to use the bottom non-blank cell in a range. How can this be achieved?

No luck finding an answer? You could always try Google.