Autofilter Criteria as String Value

**petesguitar1** · 05-03-2010, 08:05 PM

G'day and thanks in advance

I have a list of stores in one table. I have Problem tickets with associated store numbers in another. I want to be able to press a button to autofilter by store number and have it cycle through the list i.e.

Pr Store Store list
A 1 1
B 1 2
C 1 3
D 2 4
E 2 5
F 3 6
G 5 7

So I would push the button and it would filter all problems at store 1. Bush the button again and it would be store 2. Push again and store 3. The list to scroll through is Store List.

I imagined I could use a for / next type loop and declare the autofilter criteria as a String - but I am hopelessly lost.

Thanks heaps

**Simon Lloyd** · 05-03-2010, 08:11 PM

Why not add a workbook?
Providing a workbook will not only get you your answer quicker but will better illustrate your problem, usually when we can see your data (it can be dummy data but must be of the same type) and your structure it is far easier for us to give you a tailored, workable answer to your query

**petesguitar1** · 05-03-2010, 10:11 PM

Here is a simplified version, but should give you the idea. What I need to do is be able to Autofilter by FIRST_CONTACT_ID - The trick for me is to not have to scroll through the Autofilter box each time - I would like it to press a button and it would go onto the next unique FIRST_CONTACT ID and the associated problem numbers.

**JBeaucaire** · 05-04-2010, 12:12 AM

Here is a "rotateautofilter" macro and a "showall" macro attached to buttons on your sheet.

Please Login or Register  to view this content.

**petesguitar1** · 05-04-2010, 01:31 AM

Hey, thanks heaps. With a little tweaking this is working great.

**JBeaucaire** · 05-04-2010, 02:11 AM

Yeah, I realized I misread which columns you wanted to filter by. I edited the macro to make it more robust and easy to edit.

The final code is here.

Please Login or Register  to view this content.

**estgas** · 03-09-2017, 06:16 AM

Hi JB, this code looks great!
1. However the main file for which I would use it, contains dates and I would like to auto-filter by progressive MONTH_YEAR, any idea how to make it? (Now it does not auto-filer even on Day_Month_Year level).
2. Any chance that code would automatically recognize the active auto-filter column? i.e. without specifying it in VBA.
3. Some tables do not start on row 1,any possibility that code would work also on tables starting on any row? Of course user would need to select the heather table cell that he/she is interested in auto-filtering.
Many many thanks for your support/attention.
Kr, estgas

**FDibbins** · 03-09-2017, 01:02 PM

estgas welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.