Set AutoFilter Field in Excel to a range name?

  • Thread starter Thread starter Walter Wells
  • Start date Start date
W

Walter Wells

Is it possible to set the field:= to a range name? using auto filter.

I have create some macros to filter a column based on a text string in
a cell. These macros are assigned to a customized toolbar: The code
is as follows:

Range("F1").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 1
Selection.AutoFilter Field:=6, Criteria1:="<text string>"

What I would like to do is set the 'Field:=6' to 'Field:=<range name>'
(if possible) so If additional columns are inserted the filter will
still work using the range name rather than the field number (which
get put out of sync when you insert additional columns.

Any help appreciated
 
If you look at VBA's help, you'll see that Field:= expects an integer.

But you could use the column that holds the first cell in that named range and
do a little arithmetic to get that integer:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myField As Long

With ActiveSheet
Set myCell = .Range("testname1")
.AutoFilterMode = False
Set myRng = .Range("F1").CurrentRegion
If Intersect(myCell, myRng) Is Nothing Then
MsgBox "named range isn't part of autofilter range"
Else
myField = myCell.Column - myRng.Column + 1
myRng.AutoFilter Field:=myField, Criteria1:="<criteria here>"
End If
End With

End Sub
 
Back
Top