Hello,
Is it possible in VBA to sort a list which excludes names from a named range?
I have a list of names, which includes blanks, a department, a code, and a number of coworkers that don't have to be taken into consideration. Therefore, I would like to blind these data.
In my code, the sorting works fine without the named range part (criteria 3). The names-list is too long and volatile, so I would prefer not to hard code the names to be excluded. I put them in a Named Range, called "Excluded" on a separate tab.
This is a relevant part of my code so far (the Criteria3:="<>" & excl does not work. It gives "Run-time error '1004': Application-defined or object-defined error")
Is it possible in VBA to sort a list which excludes names from a named range?
I have a list of names, which includes blanks, a department, a code, and a number of coworkers that don't have to be taken into consideration. Therefore, I would like to blind these data.
In my code, the sorting works fine without the named range part (criteria 3). The names-list is too long and volatile, so I would prefer not to hard code the names to be excluded. I put them in a Named Range, called "Excluded" on a separate tab.
This is a relevant part of my code so far (the Criteria3:="<>" & excl does not work. It gives "Run-time error '1004': Application-defined or object-defined error")
VBA Code:
Sub Sort()
Dim excl As String
Set excl = ThisWorkbook.Names("Excluded")
With Range("A1", Cells(lastRow, lastCol)).Select
Selection.AutoFilter Field:=1, Criteria1:="<>" '(Blanks)
Selection.AutoFilter Field:=2, Criteria1:="<>*ICT", Criteria2:="<>GA00*", Criteria3:="<>" & excl
End With
End Sub