Problems using filters

[johnwhite]

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

 

[pgc01]

Hi John
Welcome to the board

First problem: I usually trap the error of ShowAllData

    On Error Resume Next
    ActiveSheet.ShowAllData

To remove the autofilters try:

ActiveSheet.AutoFilterMode = False

Hope this helps
PGC