Problem Using Auto Filter

[alishehzad]

Hi Everyone,

I have come across a strange problem while using the Autofilter in
Excel Macros.

I have to Filter my data based on two columns. In One column i need to
place the filter with Entries equallying #N/A. And in the second one
Entries NOT Equal to Zero.

THE PROBLEM is that whenever i apply the filter .... the LAST Entry of
the list Does NOT get filtered.

I have tried to use different approaches like
-------------------- Code -------------------------------
Range("B1").Select
Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:="#N/A"
Range("J1").Select
Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:="<>0",
Operator:=xlAnd
-------------------------------------------------------------

or

----------------------- Code -----------------------------
Rows(1).AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="#N/A"
Selection.AutoFilter Field:=10, Criteria1:="<>0", Operator:=xlAnd
-------------------------------------------------------------
and even
----------------------- Code -----------------------------
Cells.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="#N/A"
Selection.AutoFilter Field:=10, Criteria1:="<>0", Operator:=xlAnd
-------------------------------------------------------------

But in all cases the last Row doesnt get filtered. Ive tried to change
the number of rows by deleting some entries ( I thought perhaps filter
has a limit) But what ever the number of rows, the last row doesnt get
filtered.

Am I making some stupid mistake ? or is there some problem with my
Excel ???

Kindly HELP !

 

[Bernie Deitrick]

Ali,

The problem that you describe sounds like you have a SUBTOTAL function in
one or more columns. The solution is to exclude that column from the range
being filtered. So, if your column A has the SUBTOTAL functions, then use

Range("B1:H100").Autofilter.....

instead of

Range("A1:H100").Autofilter....

HTH,
Bernie
MS Excel MVP

 

[alishehzad]

Yes Bernie, In ONE of the two columns that im using Autofilter ( the
one with "<>0") the data is the subtotal of four other columns.

I didnt get the solution you proposed ? Would u mind explaining a
little ? and Wud it help if I Copy and Paste that columns, pasting the
VALUES, so that the subtotal function is done away with altogether ??

Thanks for ure help in advance...

 

[Bernie Deitrick]

Ali,

Let's say that you want to filter columns A to H, and your SUBTOTAL formulas are in column G. You
can

1) change the SUBTOTAL formulas to values prior to filtering, but then you lose the usefulness of
the SUBTOTAL function, which works on visible cells only.
2) move column G to beyond your current H, or before your current A, and then when you apply the
filters, do not select that column.

HTH,
Bernie
MS Excel MVP