Log In
Register
Home
June 15, 2022
Offline
Is it possible to just exclude a value from a filter without specifying all the other values that are included?
I want to include all values, except "Closed/Transferred"
I want to use it on many sheets I use, in all cases the other values vary, and even the "Closed/Transferred" is not always field 7
This is a recording:
ActiveSheet.ListObjects("InputTable4").Range.AutoFilter Field:=7, Criteria1 _
:=Array("1. Allocated", "2. Assessment Ongoing", _
"3. Assessment Completed, Ongoing Work", "4. Support Planning", "5. To Be Closed" _
, "6. Safeguarding/VARM/COP/Finance", "7. On Recovery Plan", "="), Operator:= _
xlFilterValues
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
Yes, you can simply use the <> operator:
ActiveSheet.ListObjects("InputTable4").Range.AutoFilter Field:=7, Criteria1:="<>Closed/Transferred"
June 15, 2022
Offline
That looks great, I just want to exclude values that are "<>Closed/Transferred"
but what is the referral to Field 7???
Ive tried deleting it but it generates an error?
Any ideas?
Edit:
The field heading is always "Current Stage in Process", but not always 7
"
Trusted Members
Moderators
November 1, 2018
Offline
Your existing code used Field:=7 so I used the same. If your table layout might change, then you can get the index from the listcolumn like so:
Dim tb as Listobject
set tb = ActiveSheet.ListObjects("InputTable4")
tb.Range.AutoFilter Field:=tb.listcolumns("Current Stage in Process").index, Criteria1:="<>Closed/Transferred"
Answers Post
1 Guest(s)
