vba help - filter via dictionary

[Mallesh23]

Hi Team,

How to filter Data using Dictionary Keys. I got this code in this forum only.
Here I want to filter dictionary Keys and delete the the Data.


Exclude List:=> Case "Dhoni", "Sachin", "Virat"
.AutoFilter Field:=6, Criteria1:=d.keys(), Operator:=xlFilterValues Getting Error at this line.

Sub AF()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    a = .Columns(1).Value
    For i = 2 To UBound(a)
      Select Case a(i, 1)
        Case "Dhoni", "Sachin", "Virat"
        'Case Else: d(a(i, 1) & "") = 1
        Case Else: d(a(i, 1)) = 1
      End Select
    Next i
    .AutoFilter Field:=6, Criteria1:=d.keys(), Operator:=xlFilterValues   ' Filter not working here
  End With
  
  Range("a1").CurrentRegion.Offset(1).EntireRow.Delete
  
  
End Sub

Below is a Sample data Testing Above Code.

Book1
	A	B
1	Name
2	Sachin	keep
3	Dhoni	keep
4	Virat	keep
5	Kohli	Delete
6	Bumrah	Delete
7	Gayle	Delete
8	Ponting	Delete
9	Gilchrist	Delete
10	David Warner	Delete
Sheet1

Thanks
mg

 

[RoryA]

Your filter range needs to include the header row in row 1, and you can't filter on Field 6 if you only have one column, so:

Sub AF()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    a = .Columns(1).Value
    For i = 2 To UBound(a)
      Select Case a(i, 1)
        Case "Dhoni", "Sachin", "Virat"
        'Case Else: d(a(i, 1) & "") = 1
        Case Else: d(a(i, 1)) = 1
      End Select
    Next i
    .AutoFilter Field:=1, Criteria1:=d.keys, Operator:=xlFilterValues   ' Filter not working here
  End With
  
  Range("a1").CurrentRegion.Offset(1).EntireRow.Delete
  
End Sub

 

[Mallesh23]

Hi Rory,

Thanks for your help, Code worked in below two situation.

When to use &"" Operator.?....

'Case Else: d(a(i, 1)[B] & "") = 1  'When to use & "", [/B]I didn't see any change in output
        Case Else: d(a(i, 1)) = 1

Thanks
mg

 

[RoryA]

You only need that if you specifically need to coerce the value into a String - eg the cell contains 1 and you want "1" as the key for some reason.

 

[Mallesh23]

Hi Rory,

Perfect ! Thanks once again for your help, This cleared my doubt. ?


Thanks
mg