Delete multiple rows based on one cell value

abarbee314

New Member
Joined
Apr 5, 2013
Messages
24
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 :)

NameStatusState
AaronRight-handedFlorida
AaronSnarkyFlorida
AaronBrunetteFlorida
SallyBlondeNew York
ShawnBrunetteHawaii
ShawnSnarkyHawaii
MaryBrunetteKansas
CarmenRight-handedMissouri
KaitlinBlondeIllinois
KaitlinRight-handedIllinois
 
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.
VBA Code:
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
 
Upvote 0
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!

NameStatusState
AaronRight-handedFlorida
AaronSnarkyFlorida
AaronBrunetteFlorida
SallyBlondeNew York
ShawnBrunetteHawaii
ShawnSnarkyHawaii
MaryBrunetteKansas
CarmenRight-handedMissouri
KaitlinBlondeIllinois
KaitlinRight-handedIllinois
 
Upvote 0
Sorry about the confusion, I answered my own question. Please see Post #11
 
Upvote 0
Another option

VBA Code:
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!! :)
 
Upvote 0
Similar to @igold 's approach, adding another condition to my method is as simple as adding an OR statement. Just change this:

VBA Code:
If ar(i, 2) = "Snarky" Then

to this

VBA Code:
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.
 
Upvote 0

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