VBA Autofilter array question

[woods2007]

Hi,

Is there a way to use a 'not' with an array in autofilter?

I'd like to show all rows excluding those in the array.

see below code:

Sub filtertitle()

Dim lr As Long

lr = Cells(Rows.count, 1).End(xlUp).Row

[a1].CurrentRegion.Select

Selection.AutoFilter

Range(Cells(1, 1), Cells(lr, 26)).AutoFilter Field:=2, Criteria1:=Array("Mr", "Mrs", "Miss", "Ms", "Dr"), Operator:=xlFilterValues

End Sub


I have a work around by adding an additional column and filtering on my criteria and then adding a '1' in that column and then filter on NOT '1' but its a bit long winded. Is there a more direct route?

Stephen

 

[RoryA]

No, not with an autofilter. Advanced filter can be easier to use with such situations although it does require a separate criteria range.