Can I name an AutoFilter Field?

[DUI]

Hi Guys,

Rather than using:

    Selection.AutoFilter Field:=24, Criteria1:="2"

is there a way I can use the heading name of that column instead of its' Field number..?
Every time I insert or move a column I have to change all these numbers...

Thanks in advance.

John.

 

[p45cal]

Yep, the likes of:

Selection.AutoFilter Field:=Application.Match("Header 2", Selection.Parent.AutoFilter.Range.Rows(1)), Criteria1:="2"

with an error message:

Sub test()
ColHeadr = "Header 2"
Set xxx = Selection.Parent.AutoFilter.Range.Rows(1)
Set yyy = xxx.Find(ColHeadr)
If Not yyy Is Nothing Then
  Selection.AutoFilter Field:=Application.Match(ColHeadr, xxx), Criteria1:="2"
Else
  MsgBox ColHeadr & " doesn't exist in the autofilter"
End If
End Sub

I've stuck with 'selection' following your example, but you could tweak it to use a sheet-qualified range instead, that way the sheet wouldn't need to be the active sheet and no selection would be necessary.

Hi Guys,

Rather than using:

    Selection.AutoFilter Field:=24, Criteria1:="2"

is there a way I can use the heading name of that column instead of its' Field number..?
Every time I insert or move a column I have to change all these numbers...

Thanks in advance.

John.

 

[DUI]

Thanks for your help p45cal,

I also got this working:

Sub testname()


Dim rng As Range, res As Variant
Set rng = Range("C11:X11").Cells
res = Application.Match("Header1", rng, 0)
If Not IsError(res) Then
Selection.AutoFilter Field:=res, Criteria1:="2"
Else
MsgBox "Header1 was not found"
End If

End Sub

Not sure if one is better than the other...

Regards,

John.

 

[p45cal]

Your error trapping is more succinct than mine, but your setting is hard coded, so you'll still have the problem of changing the code when you add columns.
The autofilter object sits under the sheet object (since there's only one per sheet, no problem there), so however big or wherever the autofilter is on the sheet the code won't need changing if you use just the sheet to identify the autofilter range.

Sub test2()
Dim rng As Range, res As Variant
Set rng = ActiveSheet.AutoFilter.Range.Rows(1)  'qualify the sheet any way you want, can be any sheet, no selection needed.
res = Application.Match("Header1", rng, 0)
If Not IsError(res) Then
  rng.AutoFilter Field:=res, Criteria1:="2"
Else
  MsgBox "Header1 was not found"
End If
End Sub

 

[DUI]

Your right p45cal, makes perfect sense.
Thanks...