Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
(1) Is this a limitation of Excel, in that only one range per worksheet can be advance filtered, or am I missing something simple here?
(2) If there is such a limitation, then I would place each of the 5 data sets on its own worksheet and filter them individually (no big deal), but then would need to copy those 5 filtered subsets to a single worksheet and I am not sure how to do that either.
This would all be done in VB code.
Any thoughts or wisdom? Thanks in advance.
Tom Urtis
> (1) Is this a limitation of Excel, in that only
> one range per worksheet can be advance filtered,
> or am I missing something simple here?
One filtered list per worksheet appears to be the
case. Excel is doing more than just hiding rows.
You'll notice that the filtered, visible row numbers
become blue. Also, Excel's Copy/Delete command
behave differently on a filtered list. I suspect
that there's a modal aspect to filtering that prevents
the filtering of more than 1 list per worksheet.
> (2) If there is such a limitation, then I would
place each of the 5 data sets on its own worksheet
and filter them individually (no big deal), but
then would need to copy those 5 filtered subsets
to a single worksheet and I am not sure how to do that either.
You might what to take a look at...
and then...
Hi Tom
I'm not too sure I understand you here. If you use Advanced Filter on say range A4:D20 with a criteria set for each Column, you will get the expected result. If you then Try to apply Advanced Filter to another range it will of course overide you previous filtred range, but what else could it do??
Confused
Dave
OzGrid Business Applications
Dave --
Thanks for your message. As you say (and Mark W also noted) there is a limit of 1 filtered list per worksheet. Until now I quietly assumed that (for instance) criteria range A3:C4 could be used to filter data range A7:C30, and, elsewhere on the same worksheet, criteria range X100:Z101 should also be able to filter range X105:Z150. Without having thought about it before I wrote the original post, it seemed that more than one filtered list per worksheet should be do-able, given Excel's other capabilities. No problem though, I'm now using separate worksheets for each filter list, and have copy > pasted the 5 sets of filtered data to other places in the workbook where I wanted that filtered data to appear.
Thanks again to you and Mark.
Tom
Tom, I must be missing something here!
>criteria range A3:C4 could be used to filter data range A7:C30, and, elsewhere on the same worksheet, criteria range X100:Z101 should also be able to filter range X105:Z150
It can! so long as all the headings are the same as your criteria range. But you have to realise that if you filter say A7:C30 first, then filter range D7:F100 any rows which do not match the criteria in your second filter (D7:F100) will be hidden and this will naturaly override any Visible rows in your first range (A7:C30). As I said, what else can it do? Advanced and Auto filter hide the ENTIRE row, not just the cell.
If your second range was BELOW your first then there is no conflict.
Dave
OzGrid Business Applications