Problems using filters

johnwhite

New Member
Joined
Sep 25, 2006
Messages
27
Hi,

i have prepared a spreadsheet and configured the autofilter so that i can display only the rows selected in the filter... no problem

what i have tried to do is create objects with assigned macros to them to make the filtering easier for the very non technical people who will be accessing the spreadsheet. for example..

[reset all filters]
[show only records with A in col3]
[show only records with B in col3]

by recording macros of myself selecting from the menu Data>Filter>Showall I can get the reset button to work. The first problem comes if this button is clicked when the data is already 'all there' (ie no filters to remove). I would ideally have just liked nothing to happen in this instance, but instead i get a runtime error 1004 ShowAllData method of worksheet class failed.

the second problem is that, due to the nesting of filtration operations, i would need each of the [show only records with X in col3] buttons to first remove all filters before applying the new one. Again I have tried to record this macro but receive the same error as mentioned above.

any suggestions would be greatly appreciated.

Thanks,

John
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi John
Welcome to the board

First problem: I usually trap the error of ShowAllData

Code:
    On Error Resume Next
    ActiveSheet.ShowAllData

To remove the autofilters try:

Code:
ActiveSheet.AutoFilterMode = False

Hope this helps
PGC
 

Forum statistics

Threads
1,136,346
Messages
5,675,228
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top