AutoFilter macro

[03021979]

Let's assume that a certain range is with autofilter option on. There can be many values (the number of these values may change). How to write a code to go through each value in this autofilter?

 

[Gavona]

To do what you are asking I would use advanced filter, "copy to another location", "Unique records Only". That would give a list to loop through.

Beware that there is a limit to how many items display in the dropdown list of an autofilter. Not sure if that would impact on a solution based around autofilter.

If you are trying to loop through them all then I wonder if sorting and sub-totalling ot a pivot table would be easier. What are you trying to achieve?


Gavin

 

[03021979]

but I need to have it as a VB macro because I need to switch through all values in this autofilter and print a sheet with each autofilter criteria...

 

[xlbo]

yup - that is what Gavin has suggested. USe VBA to automate the ADVANCED filter which gives you a list of unique values which you can then loop through.

Have a look at the help file for advanced filter, try it out and record yourself performing the required actions with the macro recorder. If you have any specific issues with the code it produces, post back here

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Actually post any questions relating to VBA in forum707.

If you are only filtering on 1 column and the aim is to simply print each set of results out then I still say that there may be some other options that are easier.

Try making a copy of your worksheet, sorting the data, applying sub-totals (choosing to have a 'pagebreak between groups'). You can then delete or blank the rows with the subtotals so you would have a result very similar to that you seem to be describing - and with a lot less effort.


Gavin