First of all it looks like you might be missing a closing parenthesis.
Here's how I would do it. Make a pivot table on your data with totals. The only thing in this pivot table should be the department name in the row area. This gives you a unique list of departments. I did this on a tab called pivots. I took the results and made a named dynamic range out of them called Departments. =OFFSET(Pivot!$A$2,0,0,COUNTA(Pivot!$A:$A)-1,1).
Then I used the following VB code to generate a list of departments other than department 4.
Function Department_List() As String
Dim cl As Range
Dim Str As String
Str = ""
For Each cl In Range("Departments")
If cl.Value <> "Department 4" Then
Str = Str & Chr(34) & cl.Value & Chr(34) & ", "
End If
Next
Department_List = Left(Str, Len(Str) - 2)
End Function
You should be able to plug this function into your code.
ActiveSheet.Range("$A$1:$AA$2046").AutoFilter Field:=2, Criteria1:=Array(Deparment_List) Operator:=xlFilterValues
Bookmarks