Advanced Filters

[Guest]

I am using macros to apply advanced filters to datasets. However, I was
wondering if it is possible to sort two different ranges in the same
worksheet at the same time. This would avoid creating many, many tabs. Thanks!

Charles

 

[Guest]

Hi Charles -

A sorting application such as yours is the perfect place to use Excel's
macro recorder to generate a visual basic macro to do the sorting. Choose
|Tools|Macro|Record New Macro... and click [OK]. Then manually sort one
range, then the other (as you would normally in Excel). Then Stop the macro
recorder (a toolbar with the [Stop] button should be 'floating' on your
screen during the recording process). Check the macro that it produces in
the VB editor (Alt-F11 and browse to the module containing your recorded
macro; typically Module1) and tune it if necessary.

Let us know if you need help with this.

 

[Guest]

Jay:

Thanks for the response. I have already recorded the macro, and tweaked it.
The problem, however, is that the filter will only filter one list at a time.
It will not keep both lists filtered in different ranges on the same sheet. I
am trying to figure out if this is possible. Have you encountered this
problem or do you know if you can have more than one advanced filters working
in the same sheet?

Charles

Hi Charles -

A sorting application such as yours is the perfect place to use Excel's
macro recorder to generate a visual basic macro to do the sorting. Choose
|Tools|Macro|Record New Macro... and click [OK]. Then manually sort one
range, then the other (as you would normally in Excel). Then Stop the macro
recorder (a toolbar with the [Stop] button should be 'floating' on your
screen during the recording process). Check the macro that it produces in
the VB editor (Alt-F11 and browse to the module containing your recorded
macro; typically Module1) and tune it if necessary.

Let us know if you need help with this.
--
Jay

I am using macros to apply advanced filters to datasets. However, I was
wondering if it is possible to sort two different ranges in the same
worksheet at the same time. This would avoid creating many, many tabs. Thanks!

Charles

 

[Guest]

Hi Charles -

I wasn't able to find any technical articles that describe that the limit is
one filtered list per worksheet, but as you've discovered, the functional
limit is one; if you attempt to filter a second list, the filter is
automatically removed from the first list. This limit would translate into a
VBA limitation.

I did find a number of posts scattered about that simply state that one is
the limit. I have personally never used more than one because of a distant
recollection of reading this somewhere and because of the automatic
constraint. So, until we can find a technical reference or input from
others, I guess we'll just have to accept the functional limit. I'll keep my
eyes open for the information, though.

I can't think of a best work-around. I suppose you could use macros and
hidden sheets to emulate two filtered lists; other than that, I'm stumped.
Let me know if you need assistance with that work-around.

--
Jay

Jay:

Thanks for the response. I have already recorded the macro, and tweaked it.
The problem, however, is that the filter will only filter one list at a time.
It will not keep both lists filtered in different ranges on the same sheet. I
am trying to figure out if this is possible. Have you encountered this
problem or do you know if you can have more than one advanced filters working
in the same sheet?

Charles

Hi Charles -

A sorting application such as yours is the perfect place to use Excel's
macro recorder to generate a visual basic macro to do the sorting. Choose
|Tools|Macro|Record New Macro... and click [OK]. Then manually sort one
range, then the other (as you would normally in Excel). Then Stop the macro
recorder (a toolbar with the [Stop] button should be 'floating' on your
screen during the recording process). Check the macro that it produces in
the VB editor (Alt-F11 and browse to the module containing your recorded
macro; typically Module1) and tune it if necessary.

Let us know if you need help with this.
--
Jay

I am using macros to apply advanced filters to datasets. However, I was
wondering if it is possible to sort two different ranges in the same
worksheet at the same time. This would avoid creating many, many tabs. Thanks!

Charles