Macro to show all on an autofilter?

per3 · May 24th, 2002, 06:49 AM

I have an excel database that is contained within a single worksheet that utilized the autofilter as sort of a query tool. Instead of having to manually go to the data window or each drop-down list to show all, I would like to make a button that, once pressed, would show all and refresh the database back to its original data. I tried using the record feature, but I get an error in the active sheet line of code. I don't know much visual basic, so is there code for this type of thing? Thank you in advance!

kkknie · May 24th, 2002, 08:55 AM

I've not done much with autofilters in code other than create them, so I'm not much help. On the other hand, in excel's help files, search for AutoFilterMode and check out some of the code examples (especially the Autofilter, Filter and Filters Collection objects). They may get you on the right track.

In any event, this reply gets your questions back to the top of the board...

K

Ben · May 24th, 2002, 09:06 AM

I think that the visual basic line below would do the trick

Sheets("sheet name").ShowAlldata
End Sub

"Sheet name" being the name of your worksheet

lenze · May 24th, 2002, 09:08 AM

Ther is already a custom button in Excel that does this. Right Click on any tool bar and choose Customize. On the Commands tab, choose Data and locate the Show All button. Drag it to any toolbar. Click Close.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 24th, 2002, 06:49 AM	#1
per3 New Member Join Date: May 2002 Posts: 10	I have an excel database that is contained within a single worksheet that utilized the autofilter as sort of a query tool. Instead of having to manually go to the data window or each drop-down list to show all, I would like to make a button that, once pressed, would show all and refresh the database back to its original data. I tried using the record feature, but I get an error in the active sheet line of code. I don't know much visual basic, so is there code for this type of thing? Thank you in advance!

May 24th, 2002, 08:55 AM	#2
kkknie Board Regular Join Date: Apr 2002 Location: Greenwood, SC Posts: 677	I've not done much with autofilters in code other than create them, so I'm not much help. On the other hand, in excel's help files, search for AutoFilterMode and check out some of the code examples (especially the Autofilter, Filter and Filters Collection objects). They may get you on the right track. In any event, this reply gets your questions back to the top of the board... K

May 24th, 2002, 09:06 AM	#3
Ben New Member Join Date: May 2002 Posts: 37	I think that the visual basic line below would do the trick Sheets("sheet name").ShowAlldata End Sub "Sheet name" being the name of your worksheet

May 24th, 2002, 09:08 AM	#4
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	Ther is already a custom button in Excel that does this. Right Click on any tool bar and choose Customize. On the Commands tab, choose Data and locate the Show All button. Drag it to any toolbar. Click Close.