Inverse / Reverse of the filtered items

[nabeelzz]

good day all,

i need some macro to show me the reverse of current applied filter.
For example

i have a table and one column in table shows Dep A, Dep B, Dep C, Dep D etc.
i apply filter on this column for Dep A and Dep C..... So Dep B and Dec D will be hidden.
From here, i need a macro which will show me Dep B and Dep D which were previously filtered out (hide/filter out Dep A and Dep C).

Assume i have a lot of departments then its a pain to go back and see the inverse of what you previously filtered.

But that's not completely it.

Further, assume that i have applied a filter in Col A and then applied further filter in Col C. Now i want to see the reverse of applied filter for Col C only, not for Col A. Col A's applied filter should be intact.

Saying it another way, that i would like to invert the applied filter for the column in which i select a cell, but not touch other applied filters in Columns other than the selected cell.

i hope i explained myself properly.

Any help is much appreciated.

 

[Fluff]

How about

Sub ChangeFilters()

   Dim WS As Worksheet
   Dim ArrCrit As Boolean
   Dim Crit As Variant, Crit2 As Variant
   Dim Cl As Range
   Dim dic As Object, c As Variant
   
   Set WS = Worksheets("[COLOR=#ff0000]pcode[/COLOR]")
   With WS.AutoFilter.Filters.Item(ActiveCell.Column)
      If Not .On Then MsgBox "No filter values": Exit Sub
      Crit = .Criteria1
      If .Operator = 7 Then
          ArrCrit = True
      ElseIf .Operator = 1 Or .Operator = 2 Then
          Crit2 = .Criteria2
          ArrCrit = False
      End If
   End With
   
   Set dic = CreateObject("scripting.dictionary")
   If ArrCrit Then
      For Each c In Crit
      dic.Add Right(c, Len(c) - 1), Nothing
      Next c
   Else
      dic.Add Right(Crit, Len(Crit) - 1), Nothing
      If Not IsEmpty(Crit2) Then dic.Add Right(Crit2, Len(Crit2) - 1), Nothing
   End If
   With CreateObject("scripting.dictionary")
      For Each Cl In Intersect(WS.AutoFilter.Range.Offset(1), Columns(ActiveCell.Column))
         If Not dic.exists(Cl.Value) And Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      WS.AutoFilter.Range.AutoFilter ActiveCell.Column, .keys, xlFilterValues
   End With
End Sub

Change sheet name in red to suit

 

[nabeelzz]

Many thanks Fluff
it works, very nice

However, is it possible to make it dynamic.
i will make this macro stay in my personal workbook (along with a lot of other macros) which is always open in background.
and i will either create a keyboard shortcut for this macro or keep a shortcut on quick access toolbar.
any idea how that will be possible.

 

[Fluff]

If you make this change

Set Ws = ActiveSheet

It will work on the active sheet regardless of where the code is stored

 

[nabeelzz]

tried few things like as you said in the code
couldn't make it work

getting error message Compile Error: Ambiguous name detected ChangeFilters

 

[Fluff]

Sounds like you have 2 macros in the same module called ChangeFilters
You'll need to change the name of one of them.

 

[nabeelzz]

yeah, i realized just after posting in the thread.
then i fixed it
but still it does not work
it only works in the worksheet where the macro exists
not in the other sheet in another workbook
it says, application defined or object defined error.

 

[nabeelzz]

if i debug this is the line where i get error while running macro in another sheet in another workbook

For Each Cl In Intersect(WS.AutoFilter.Range.Offset(1), Columns(ActiveCell.Column))

 

[Fluff]

The macro needs to go in a regular module, not a sheet module, nor the ThisWorkbook module.
I've also made a slight change

Sub ChangeFilters()

   Dim Ws As Worksheet
   Dim ArrCrit As Boolean
   Dim Crit As Variant, Crit2 As Variant
   Dim Cl As Range
   Dim dic As Object, c As Variant
   
   Set Ws = ActiveSheet
   With Ws.AutoFilter.Filters.Item(ActiveCell.Column)
      If Not .On Then MsgBox "No filter values": Exit Sub
      Crit = .Criteria1
      If .Operator = 7 Then
          ArrCrit = True
      ElseIf .Operator = 1 Or .Operator = 2 Then
          Crit2 = .Criteria2
          ArrCrit = False
      End If
   End With
   
   Set dic = CreateObject("scripting.dictionary")
   If ArrCrit Then
      For Each c In Crit
      dic.Add Right(c, Len(c) - 1), Nothing
      Next c
   Else
      dic.Add Right(Crit, Len(Crit) - 1), Nothing
      If Not IsEmpty(Crit2) Then dic.Add Right(Crit2, Len(Crit2) - 1), Nothing
   End If
   With CreateObject("scripting.dictionary")
      For Each Cl In Intersect(Ws.AutoFilter.Range.Offset(1), Ws.Columns(ActiveCell.Column))
         If Not dic.exists(Cl.Value) And Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      Ws.AutoFilter.Range.AutoFilter ActiveCell.Column, .keys, xlFilterValues
   End With
End Sub

 

[nabeelzz]

Yaaay !!! Finally it works. has to be housed in a macro module.
Many thank Fluff

i m a C.A. not an IT person, but i have a good reputation of being good in excel. However, after reading your code, i realize i know very little.
Many thanks man.