VBA Dictionary - Delete Rows

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

Column("A:B") is my Data, Column A Consist of Player Names.

I want to delete All player Excluding Player Names :=> {"Sachin,Dhoni,BretLee,Peterson"}
I want complete Row to delete .

How to achieve this task via dictionary or array. I know normal loop, trying to learn via Dictionary method.

Sample output shown in Column "F:G", output should remain in Column A and B.

Thanks in advance for help.

Below is my Sample Data.

Book1
ABCDEFG
1Player NameScoresPlayer NameScores
2Jaysurya13891Sachin19248
3Dhoni8132Gayle12543
4Sachin19248Peterson14824
5Sehwag18357Sachin9387
6Gayle12543Peterson9677
7Peterson14824Sachin13700
8Dhoni11927Peterson14992
9David18854Sachin15534
10Jaysurya19913Peterson12280
11Sehwag12880BretLee8608
12Sachin9387Sachin15975
13Peterson9677BretLee19277
14David16228Peterson15604
15Sachin13700Sachin13626
16Peterson14992BretLee12154
17Sachin15534Peterson9257
18Peterson12280BretLee18626
19Dhoni12173BretLee17220
20Sehwag11540Peterson15379
21BretLee8608Sachin10339
22Sehwag14310Sachin11329
23David12559Peterson13656
24Sachin15975Peterson19600
25Dhoni16170Peterson16197
26Jaysurya12763Peterson8324
27Jaysurya13236BretLee16026
28Jaysurya13260Peterson18839
29BretLee19277Sachin14447
30Peterson15604Sachin10515
31Sachin13626Gayle8207
32Dhoni15136Peterson12335
33BretLee12154Gayle8530
34Peterson9257BretLee14191
35BretLee18626Gayle14427
36Jaysurya17155Gayle10789
37BretLee17220BretLee12489
38Dhoni18238BretLee12719
39Peterson15379Gayle15323
40Sehwag17226Gayle11791
41David12552Gayle17844
42Sachin10339Sachin14703
43Sachin11329Sachin15572
44Peterson13656Peterson10172
45Peterson19600Gayle8903
46Sehwag10812BretLee12792
47Jaysurya16750Peterson19201
48Peterson16197BretLee18373
49David14829Gayle8263
50Peterson8324Peterson14352
51Dhoni15639Sachin12931
52BretLee16026BretLee12577
53David18335Gayle16070
54Peterson18839
55Sachin14447
56Sehwag18568
57Jaysurya9107
58Dhoni14731
59David11144
60Sachin10515
61Gayle8207
62Peterson12335
63Gayle8530
64BretLee14191
65David8131
66Dhoni10214
67Dhoni18809
68Dhoni12182
69Sehwag17764
70Jaysurya9374
71Jaysurya9715
72Gayle14427
73Gayle10789
74Dhoni18866
75David18572
76BretLee12489
77BretLee12719
78Jaysurya8902
79Gayle15323
80Gayle11791
81Dhoni12575
82Gayle17844
83Sachin14703
84Sachin15572
85Peterson10172
86Gayle8903
87BretLee12792
88Sehwag9806
89Peterson19201
90Dhoni13489
91BretLee18373
92Gayle8263
93David18320
94Dhoni15497
95Jaysurya18917
96Jaysurya8758
97Peterson14352
98Sachin12931
99BretLee12577
100Gayle16070
Sheet1


Thanks
mg
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi Fluff,

Below method gives me correct result , any other way array or dictionary , as my data is 20000 Rows

Rich (BB code):
Sub DeleteRow_With_Criteria()

    Dim lr As Long
    Dim i As Long
   
    lr = Range("a" & Rows.Count).End(xlUp).Row

    Dim ar As String
    ar = Join(Array("Sachin", "Dhoni", "Gayle", "Peterson"), "!")
   
    For i = lr To 2 Step -1
        If InStr(1, ar, Cells(i, 1).Value, vbTextCompare) = 0 Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next i

End Sub

Thanks
mg
 
Upvote 0
The best way to learn something, is to try it. So if you post what you have tried, suing the dictionary, I will have a look at it.
 
Upvote 0
How do you want the result displayed?

Should the scores still be in the order as they currently are would you want the scores for each player to be grouped together?

If it's the former then you could simply loop through an array with the existing data and only add a row from that data to a new array if the Player matches one of the names you want to keep.
VBA Code:
Sub FilterPlayers()
Dim arrIn As Variant
Dim arrOut As Variant
Dim arrPlayers As Variant
Dim cnt As Long
Dim idxRow As Long
Dim Res As Variant

    arrIn = Sheets("Sheet1").Range("A1").CurrentRegion.Value
    
    arrPlayers = Array("Sachin", "Dhoni", "BretLee", "Peterson")
    ReDim arrOut(1 To UBound(arrIn, 2), 1 To UBound(arrIn, 1))
    
    For idxRow = 2 To UBound(arrIn, 1)
        Res = Application.Match(arrIn(idxRow, 1), arrPlayers, 0)
        
        If Not IsError(Res) Then
            cnt = cnt + 1
            arrOut(1, cnt) = arrIn(idxRow, 1)
            arrOut(2, cnt) = arrIn(idxRow, 2)
        End If
    Next idxRow
    
    ReDim Preserve arrOut(1 To UBound(arrIn, 2), 1 To cnt)
    
    Range("A1:B1").Copy Range("F1")
    Range("F2").Resize(UBound(arrOut, 2), 2).Value = Application.Transpose(arrOut)
    
End Sub
 
Upvote 0
Hi Norie,

nice code, here I want to delete entire Row. where players does not exists
arrPlayers = Array("Sachin", "Dhoni", "BretLee", "Peterson")


Thanks
mg
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top