Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
AutoFilter macro
- Thread starter 03021979
- Start date
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
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
- Thread starter
- #3
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
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
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
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
- Status