Macro Button with Filter Function (It's a fun one!)

[Overkill]

Hello Good People of Excel Forum,

I'm somewhat knew to coding in excel and definitely inexperienced in creating buttons. What I'm trying to do is create a button to filter my document easily for basic users. Any of these options would be ok:

1. Click Button and filters column x for y value. Next button stacks filter for column a with z value.. etc. Final button resets filters.
2. Select cell -> click button to filter on selected cell's value, Next filter Select cell -> click button to filter.. etc. Final button resets filters.

And also need this:

3. Click Button-> take me to cell with x value. (this one i couldn't get, usually people do cell range)

The reason these are particularly needed is because my list of 2000 plus rows will need to be updated and have the filters and functions still work. It's also separately color coded so values are the key sorting variable I desire.

I think I know how to add the macro to a button once its done. Thanks!

[skywriter]

Autofilter.

[Overkill]

Unfortunately, I'm really not good at this coding thing. I need something clean and simple. Maybe like a toggle filter button that can be stacked (probably the best option) so there is no need for a reset all filters button.

[skywriter]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.

ViewPic

[Overkill]

Fake Doc1.xlsx

This is a really basic example of what I want. Just before and after tables. At the top of the document I'd like to include buttons with names: Candies we distribute, Candies they distribute, Brand 1, Brand 2, City A, City B. Etc. Hopefully these could be toggled so the sorting is easily stacked and removed.

Thanks!

[Overkill]

This code is really close to what I want. This creates a new sheet with the selected cell's value filter. It also only does the range between blank cells, whereas i want the entire column. I will try to tweak it until it works. But any help would be awesome!

Please Login or Register  to view this content.

[skywriter]

No problem thanks for posting I will look at it.

One favor to ask, please edit the post above and highlight all your code and then click the # icon and save the changes. This will surround your code in code tags, it will end up looking like this.

Please Login or Register  to view this content.

It's a forum standard and rule, it makes the code hold it's original format and makes copying it really easy.

Thanks.

[MarvinP]

Hey Overkill,

Overkill is what you are trying to do with VBA. You need to simply repeat the data in the rows and use an autofilter, like suggested above. See the attached and click on the dropdowns. Teach people how to use these dropdowns and you will save yourself and others TONS of time.

[skywriter]

Try this.

[Overkill]

I'm glad you saw my pun. The only reason I haven't already done this is because the document was created by someone else without the data repeated. I will check with my coworker to see if I'm allowed to alter the content to make this easier. thanks for the tip!

[Overkill]

Very impressive Skywriter. This is exactly what I needed.Thanks!

[skywriter]

Thanks for the rep. points.

If your original request is fulfilled please click the Thread Tools drop down box above your first post and choose solved.