Free Microsoft Excel 2013 Quick Reference

Auto Filter - how to collapse "non-blank" cells in a column?

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?

Post your answer or comment

comments powered by Disqus
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.


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

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

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



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 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.

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 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


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 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.

Dear experts,

I have several columns of data which I need to consolidate by removing all the blank cells so that non-blank cells will move to the top of the list. Any idea how it's done? Can it be done by formula alone and if yes, which formula to use?

One more thing is that this needs to be automated so that the user does not have to step in to get the desired result.

Thanks in advance!!

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?

How do I do a column range from a starting cell until the first non-blank cell?

Also, is there a function, etc. that will return the row number of the first non-blank
cell in a column?


OK..let's see if I can make this understood. I have an INPUT sheet (SHEET 1)set up on excel to enter check data (in order to print a check from excel) ie. customer #, date, amount, written amount, name, address, etc.

the data tranfers to (SHEET 2) which is the CHECK FORM. I then put a check in my printer, push print & "Voila!"

Now I would like selected data (date name amount decription) to transfer to a (SHEET 3) into the first non-blank cell in a column (or something to that effect) so that I have a record of all Manual checks that were written. Each check would be entered into four columns in the next available row, never to be overwritten.

Is there such a formula? Thanks

How to highlight blank cells in a column

Eg- range (D1:D5856)

Continued from this thread

Please help: How can i programmatically lookup the cell adress for next
non-blank cell in a range?


toreadore's Profile:
View this thread:

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.

Please help: How can i programmatically lookup the cell adress for next non-blank cell in a range?


First time here. I'm new to excel, so this will probably be very simple for most of you, but I have been trying to find an answer for hours. Any help would be much appreciated!

I need to know the value (which is a date) of the first non-blank cell in a range in a row. I have found some array formulas by searching forums/google etc., but they either return it for the whole row (which I don't want), or they return text (which I don't want), or they don't work at all.

The range is: B9:FL9. Some of the cells in this range are blank, some are "0", and some contain dates. I need to know the most recent (furthest left) cell that contains a date and what that date is. Thanks!

I'd like to know how to index the first blank cell in a column of data, so
that I can return that number as a reference for a report (to use as an index
value for returning other values in that row).

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.

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