AutoFilter for multiple criteria and blank cells

Hi Guys,

I'm trying to autofilter some data though a macro with multiple criteria, one of which is to filter out the blank cells.

My code is:

rWB.Sheets(1).Range("$A$1:$AC$37803").AutoFilter field:=9, Criteria1:=Array("<>FLY", "<>TVL", "<>"), Operator:=xlOr

It seems to only filter out the blanks though. I'm still getting FLY and TVL rows???

What am I doing wrong? Thanks in advance!

What am I doing wrong?

I don’t think you are doing anything wrong. I believe this is an Excel limitation for autofilter.

Using a set up like this I can “get rid of” two components but not three or more.

Please Login or Register  to view this content.

Using an Array “construction” I can select a number of components i.e.

Please Login or Register  to view this content.

will select "FLY", "TVL" and blank values. So you could either use an array to select all other values in your data excluding "FLY", "TVL" and blanks or you could let your macro check the values of all rows and hide those that contain "FLY", "TVL" or a blank value. Depending on how much data you have got that may take a bit of time.

As I'm not an expert on autofilter hopefully other forum members can suggest a better solution.

Alf