Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Using Auto Filter
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Using Auto Filter
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 wrote in message ups.com... 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 ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Using Auto Filter
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Using Auto Filter
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 wrote in message oups.com... 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto filter problem | Excel Worksheet Functions | |||
Auto filter problem | Excel Worksheet Functions | |||
Auto Filter Problem (was VBA help) | Excel Discussion (Misc queries) | |||
Have you had a problem with Auto Filter freezing? | Excel Discussion (Misc queries) | |||
Auto Filter problem | Excel Worksheet Functions |