Dynamic autofilter field

[merry_fay]

Hiya,

I want to set an autofilter in my macro, but the autofilter field isn't
necessarily always going to be the same number. It will always have the same
column header though.

Is there a way to change the number to the column header, or make it filter
on the 'active column'?

Selection.AutoFilter Field:=14,

Thanks
merry_fay

 

[Dave Peterson]

Maybe you could just remove any existing filter and then filter the single
column by what you want.

Don't select the entire range (multiple columns), just the column that you want
to filter.

The entire row will still be hidden/shown when you filter.

Then it's gonna be something like:

Dim wks As Worksheet
Set wks = ActiveSheet
wks.AutoFilterMode = False
ActiveCell.EntireColumn.AutoFilter field:=1, Criteria1:="asdf"

 

[Dave Peterson]

If you want those arrows on all your headers, you could use something like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim RngToFilter As Range
Dim myField As Long

Set wks = ActiveSheet

With wks
.AutoFilterMode = False
Set RngToFilter = .Range("A1:O" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)
myField = ActiveCell.Column

If myField > RngToFilter.Columns.Count Then
MsgBox "Select a cell in the right range!"
Exit Sub
End If
RngToFilter.AutoFilter field:=myField, Criteria1:="asdf"
End With

End Sub

This filters columns A:O based on the used rows in column A.