Autofilter macro

[Guest]

I am writing a macro that willl autofilter. I want to autofilter on a column
entitled "YEAR". I recorded the code below using the macro recorder

Selection.AutoFilter
Selection.AutoFilter Field:=15, Criteria1:="2005"

The problem is that the "YEAR" column is not always column 15. How can I
modify this code to look for the column entitled "YEAR"

Thanks

 

[Norman Jones]

Hi Jessica,

Try something like:

'============>>
Public Sub AAA()
Dim SH As Worksheet
Dim Rng As Range, Rng2 As Range

Set SH = ActiveSheet '<<==== CHANGE

If SH.AutoFilterMode Then
Set Rng = ActiveSheet.AutoFilter.Range.Rows(1)
Else
SH.Range("A1").AutoFilter '<<==== CHANGE
End If

On Error Resume Next
Set Rng2 = Rng.Find(What:="Year", _
After:=Rng.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
On Error GoTo 0

If Not Rng2 Is Nothing Then
Rng.Cells(1).AutoFilter , _
Field:=Rng2.Column - Rng.Column + 1, _
Criteria1:="2005"
End If

End Sub
'<<============