Running a macro for each item in an Autofilter list
I regularly produce a large sheet of data with about 15 fields/columns and
several thousand rows on it which I currently filter by ones of the key fields (which may contain up to 50 different values) and then run a macro on each of those field selections (i.e. I manually run the same macro up to 50 times). How can I automate the filter selection so I can cycle through the macro as many times as required without any manual intervention. Thanks, |
I used column 1 as the key field in this shell:
Option Explicit Sub testme01() Dim wks As Worksheet Dim myUniqueCells As Range Dim myRng As Range Dim OrigAutoFilterRange As Range Set wks = Worksheets("sheet1") With wks Set OrigAutoFilterRange = .AutoFilter.Range Set myRng = Intersect(.AutoFilter.Range, .Columns(1)) End With With myRng .AdvancedFilter Action:=xlFilterInPlace, Unique:=True Set myUniqueCells = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .Cells.SpecialCells(xlCellTypeVisible) For Each myCell In myUniqueCells.Cells .AutoFilter field:=1, Criteria1:=myCell.Value 'your code here MsgBox myCell.Value & " Is showing" Next myCell End With wks.AutoFilterMode = False OrigAutoFilterRange.AutoFilter End Sub It does assume that you do have the autofilter arrows already applied. Andrew wrote: I regularly produce a large sheet of data with about 15 fields/columns and several thousand rows on it which I currently filter by ones of the key fields (which may contain up to 50 different values) and then run a macro on each of those field selections (i.e. I manually run the same macro up to 50 times). How can I automate the filter selection so I can cycle through the macro as many times as required without any manual intervention. Thanks, -- Dave Peterson |
All times are GMT +1. The time now is 01:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com