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 !

If the last line of data contains a SUBTOTAL formula, it may not be
included in the filtered range.
You could remove the subtotal, or add another row at the end of the
range, then the filter should include that last row.

wrote:
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 !



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html