Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Andrew
 
Posts: n/a
Default 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,
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to CANCEL file SAVE PROMPT when MACRO is running? Stuart Macro Muppet Excel Discussion (Misc queries) 3 August 11th 05 12:26 PM
macro for 4 constraints in autofilter Gus Excel Discussion (Misc queries) 1 August 4th 05 01:17 PM
Keep autofilter after macro is run gmr7 Excel Worksheet Functions 2 July 5th 05 01:16 PM
Prevent A Macro From Running If SpreadSheet is Filtered carl Excel Worksheet Functions 1 June 22nd 05 04:04 PM
Loop Macro autofilter Paul. Excel Discussion (Misc queries) 2 March 25th 05 10:35 AM


All times are GMT +1. The time now is 12:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"