Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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, |
#2
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to CANCEL file SAVE PROMPT when MACRO is running? | Excel Discussion (Misc queries) | |||
macro for 4 constraints in autofilter | Excel Discussion (Misc queries) | |||
Keep autofilter after macro is run | Excel Worksheet Functions | |||
Prevent A Macro From Running If SpreadSheet is Filtered | Excel Worksheet Functions | |||
Loop Macro autofilter | Excel Discussion (Misc queries) |