Delete multiple rows based on one cell value

[abarbee314]

Hi folks! Hope you're all doing well. I'll paste a sample data set down below, but here is the scenario for it:

Real/larger data set has nearly 2000 rows and 20+ columns. Many of the names appear in 2+ rows for some reason. SOME of the rows have a value in the second column that voids their need to be in the table. We want ALL entries for the names to be removed if *ANY* of them have the voiding value.

So, below I have decided that SNARKY is the "bad" word. My need here: If SNARKY appears as the status for someone, I want all rows for that person to selectable/deletable/deleted - whatever is the cleanest or most efficient.

Based on my sample sheet, the end result should only have rows for Sally, Mary, Carmen, and Kaitlin. Shawn and I are SNARKY, so all of our rows need to be removed since at least one of them has SNARKY as the status. Hope that makes sense. THANKS, in advance, as always

Name	Status	State
Aaron	Right-handed	Florida
Aaron	Snarky	Florida
Aaron	Brunette	Florida
Sally	Blonde	New York
Shawn	Brunette	Hawaii
Shawn	Snarky	Hawaii
Mary	Brunette	Kansas
Carmen	Right-handed	Missouri
Kaitlin	Blonde	Illinois
Kaitlin	Right-handed	Illinois

 

[igold]

I am sorry I did not look at your OP. Did you want to use the same data...
This would work with your original data.

Sub DeleteSnarky()

    Dim arr, arr2, i As Long, r As Long, x As Long
    Dim wsS As Worksheet: Set wsS = ActiveSheet
    
    Application.ScreenUpdating = False
    arr = wsS.UsedRange
    r = 1
    ReDim arr2(1 To UBound(arr, 1))
    For i = 1 To UBound(arr)
        If arr(i, 2) = "Snarky" Or arr(i, 2) = "Blonde" Then
            arr2(r) = arr(i, 1)
            r = r + 1
        End If
    Next
    
    ReDim Preserve arr2(1 To r - 1)
    For x = 1 To UBound(arr2)
        For i = 1 To UBound(arr)
            If arr(i, 1) = arr2(x) Then
                arr(i, 1) = ""
            End If
        Next
    Next

    With wsS.UsedRange
        wsS.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = arr
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
    
End Sub

 

[abarbee314]

We all started in the same place!

Are SNARKY and BLONDE both in column B or are they in different columns. A small sample of your data is always a good thing .

I'm trying, man

So, yes, SNARKY and BLONDE in my sample set are both in column B. My colleague's data is litigation stuff and can't be shared, so I made up this sample table so I can swap out the phrases being filtered later. Her filtering words/phrases are also in Column B, so it should be easy for me to plug-n-play based on your final solution.

My sample was in my O.P. but I'll paste it here. Again, filtering on those values, using an OR connector, I should be left with Mary and Carmen - who are neither snarky or blonde.

THANKS!

Name	Status	State
Aaron	Right-handed	Florida
Aaron	Snarky	Florida
Aaron	Brunette	Florida
Sally	Blonde	New York
Shawn	Brunette	Hawaii
Shawn	Snarky	Hawaii
Mary	Brunette	Kansas
Carmen	Right-handed	Missouri
Kaitlin	Blonde	Illinois
Kaitlin	Right-handed	Illinois

 

[igold]

Sorry about the confusion, I answered my own question. Please see Post #11

 

[abarbee314]

Another option

Option Explicit
Sub arabee314()
    Application.ScreenUpdating = False
    Dim ar, i As Long
    ar = Range("A1").CurrentRegion
   
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(ar)
            If ar(i, 2) = "Snarky" Then
                .Item(ar(i, 1)) = .Item(ar(i, 1))
            End If
        Next i
        ar = Array(.keys)
    End With
   
    With Range("A1").CurrentRegion
        .AutoFilter 1, Array(ar), 7
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
   
    Application.ScreenUpdating = True
End Sub

Thank you very much! This was super helpful!!

 

[abarbee314]

Sorry about the confusion, I answered my own question. Please see Post #11

Thank you so much for your persistence and help. Truly appreciated!

 

[igold]

Thank you so much for your persistence and help. Truly appreciated!

I honestly enjoy helping. Feel free with any other questions you may have, it won't be a bother.

 

[kevin9999]

Similar to @igold 's approach, adding another condition to my method is as simple as adding an OR statement. Just change this:

If ar(i, 2) = "Snarky" Then

to this

If ar(i, 2) = "Snarky" Or ar(i, 2) = "Blonde" Then

Th advantaged of the Dictionary approach is that it tends to be faster than other methods.