PDA

View Full Version : Solved: Explanation of Advanced Filter



kathyb0527
08-13-2009, 11:09 AM
I've never used advanced filter before except to filter out duplicates. Now I need to filter out duplicates and anything that has a 0 or a negative number and I'm hopelessly lost. I've attached an example worksheet.

Thanks for the help!

mdmackillop
08-13-2009, 11:21 AM
Have a look at this

kathyb0527
08-13-2009, 12:37 PM
OOOHHHH! Now I get it! One more question, does the criteria range have to be on the worksheet or can it be somewhere else? I'll be looping through multiple workbooks and using the same criteria.

Thanks!:yay

mdmackillop
08-13-2009, 01:54 PM
If you are using VBA, I think the Criteria range can be anywhere.

mikerickson
08-13-2009, 02:49 PM
Both the Copy to Range (if copying to different location) and the Criteria range can be on different sheets (or different workbooks) than the List Range or each other.

When diriven by VBA, fully qualifying the various arguments of .AdvancedFiter will be sufficient.

From the keyboard:
The sheet of the Copy To Range must be active when AdvancedFilter is pressed.

If the copy to remote location option is not being used, the sheet with the Criteria Range must be active when AdvancedFilter is pressed.

If the wrong sheet is active, a misleading error message results.

As I mentioned above, when invoked from VBA, it doesn't matter which sheet is active.

kathyb0527
08-14-2009, 07:03 AM
Thank you!