How do I use advanced filter to filter for blank cells?

[Guest]

I'm using Excel 2003 for a training database. I have a relatively large
spreadsheet (+50 columns of job titles and +550 rows of SOP's) with various
various data in the cells. Each row must contain data in at least one cell
other that A#. I need to find the rows that contain blank cells from column
B across through last column, so that I can find the gaps. I can't seem to
come up with the correct criteria for this.

Monique

 

[Guest]

Try this:

With your data list in Cells A5:AX500

B1: Test4Blanks
B2: =COUNTA(B6:AX6)=0

Note: the criteria formula is in B2, but it references the first data row
under the column headings.

Select A5:AX500
<Data><Filter><Advanced Filter>
Check: Filter the list, in-place
UNcheck: Unique records Only
List Range: (already selected $A$5:$AX$500)
Criteria Range: $B$1:$B$2
Click the [OK] button

That will display records with no values in Col_B through Col_AX.

Adjust range references to suit your data.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Hi Ron,

Thank you! It works.

Monique

Try this:

With your data list in Cells A5:AX500

B1: Test4Blanks
B2: =COUNTA(B6:AX6)=0

Note: the criteria formula is in B2, but it references the first data row
under the column headings.

Select A5:AX500
<Data><Filter><Advanced Filter>
Check: Filter the list, in-place
UNcheck: Unique records Only
List Range: (already selected $A$5:$AX$500)
Criteria Range: $B$1:$B$2
Click the [OK] button

That will display records with no values in Col_B through Col_AX.

Adjust range references to suit your data.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

I'm using Excel 2003 for a training database. I have a relatively large
spreadsheet (+50 columns of job titles and +550 rows of SOP's) with various
various data in the cells. Each row must contain data in at least one cell
other that A#. I need to find the rows that contain blank cells from column
B across through last column, so that I can find the gaps. I can't seem to
come up with the correct criteria for this.

Monique