how do set an autofilter to automatically select "nonblank" cells

[tuccokeith]

How do I set an autofilter to automatically select only "nonblank" cells in a range of columns. For example for Columns A1:A1000- Z1:z1000.

Thanks

 

[xlbo]

If you scroll to the bottom of the autofilter list, there should be an option for "Non Blanks" Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[tuccokeith]

Hello,

I am looking for the vb code to set the autofilter to select "nonblanks".

I do not know the syntax to do this within a macro.

Thanks

 

[xlbo]

Aaaah - should've posted in the VBA forum then ;-)
You could've got this from recording it (which is what I just did)

Selection.AutoFilter Field:=1, Criteria1:=&quot;<>&quot; Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[GlennUK]

If you want to show rows where at least one of the cells is non-blank, but hide rows where all of the cells are blank, then add a column of calculations, where the formula would be ( if entering into cell AA2 ) ...

=IF(SUM(IF(A2:Z2<>&quot;&quot;,1,0))=0,&quot;Blank&quot;,&quot;Non-blank&quot

entered using CTRL-SHIFT-ENTER instead of ENTER, and then copy that down to the end of the required range. Re-do the filter, and filter on column AA being a value of Non-blank.

Hope that's want you wanted.

Cheers, Glenn.