Excel 2010 : Macro to rotate auto-filter

[vwhite26]

Hi

I found this article which contains an example of a macro that rotates through the auto-filter
https://sites.google.com/a/madrocket...uto-autofilter

I've almost got it working on my spreadsheet by changing 'AFCol' to 16 as I'm filtering column 'P'
But I can't figure out how to amend the code so that the auto-filter appears on row 7 instead of 1

I tried changing the last few lines from this:

Columns(AFCol).AutoFilter
Columns(AFCol).AutoFilter Field:=1, Criteria1:=CurrCrit

To this instead:

Range("P7:P500").AutoFilter
Range("P7:P500").AutoFilter Field:=1, Criteria1:=CurrCrit

Which puts the auto-filter in the right place, but it no longer works/selects the next item when run :/

Any help would be appreciated
Kind regards
Vicky

[BellyGas]

try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[vwhite26]

Thanks BellyGas

So I just got this working. I removed AFCol completely and hard coded my range in. Not good practice I know
But it's working perfectly to rotate a filter on cell P7 now

----

Option Explicit
Public CurrCrit As String

Sub AutofilterRotate()
Dim rFind As Long

Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False

If CurrCrit = "" Then
CurrCrit = Cells(8, 16).Value
Else
Columns("P:P").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("AA1"), Unique:=True
rFind = Columns("AA:AA").Find(CurrCrit, After:=[AA7], _
LookIn:=xlValues, LookAt:=xlWhole).Row
CurrCrit = Range("AA" & rFind + 1)
If CurrCrit = "" Then CurrCrit = Range("AA8").Value
End If

Columns("AA:AA").ClearContents

Range("P7:P500").AutoFilter
Range("P7:P500").AutoFilter Field:=1, Criteria1:=CurrCrit
Application.ScreenUpdating = False
End Sub