MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using Advanced Filter to find blank cells

Posted by Richard Fry on July 28, 2000 2:28 PM

My spreadsheet contains mostly dates in a mm/dd/yyyy format. I need to use the Advanced Filter to find every record in which one cell is blank in column A, or blank in column B, or in column C.

Posted by Matt Larkin on August 24, 0100 1:50 AM

To use an advanced filter on blank cells, you need to specify the criteria for each column where you want to identify a blank cell as:=

(That's right, five double quotes in a row)

This is the same as in VBA, where if you want to include the string " within code, you need to duplicate it.

So (assuming I have understood you correctly, you could have (assuming row 1 contains column labels consitent with the data):
A2 - ="="""""
B2 - <>"="""""
etc to filter non-blanks and blanks as required. Using different rows to specify your "or" choices, this would appear to work properly.

Hope this helps!


Posted by Tim Francis-Wright on July 28, 0100 2:51 PM

I'll assume that the first record of your list is in row 5, with labels on row 4.

Define a range named Criteria at a1:a2.
Leave cell A1 blank. Put the following in A2:

Then, use the Advanced Filter on your range
(it will assume automagically that you want to
use a1:a2 as the criteria range).


Posted by Richard on July 28, 0100 4:26 PM

Perhaps I should explain myself a little more. I was just using A,B,C as examples what I actually need is to know how to do that on multiple rows. I have about 30 rows that I need to search across, and 5,000 records to search through.

What I will end up doing is to use logic to show the records where A is blank, C is not blank, and D is not blank or where is blank, E is not blank, and D is not blank. . .

Thanks in for your help so far!