VBA Delete Entire Row if Contains Certain Text

[bigmacneb]

I've searched on here, but every code I put in gives me an error back. Data in column D, If any of the cells contains "Record Only" I need it to delete the entire row.
Thanks

 

[Rip-N-Burn]

Just a little "bump"

Very helpfull thread. Is there a way to "clear" entire row if column "A" CONTAINS ANY TEXT?
Or if column "A" AND "B" contains any text?

 

[rj081363]

I am running into a similiar problem....I need to delete a row if for example column "B" has specific text and column "C" has specific text then delete if one is not true then don't delete

 

[aeddipa]

I used the VBA code supplied above from the 2nd person who responded. This works great; however, I have over 50,000 rows of data and it is taking an extremely long time to process through this macro. Is there any way to speed up the overall process of this code?

Sub DeleteRowWithContents()
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Record Only" IN COLUMN D
'========================================================================
    Last = Cells(Rows.Count, "G").End(xlUp).Row
    For i = Last To 1 Step -1
        If (Cells(i, "G").Value) = "Balanced" Then
    'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
            Cells(i, "A").EntireRow.Delete
        End If
    Next i
End Sub

 

[kakan]

Hi I have used both sets of code within the thread. This successfully deletes or autofilters and deletes one set of criteria. I have the following items that I need to deleted the whole row for.
PAI, MPD, Private Contractor, Local Council. I can succesfully delete one item at a time.

Is there a way to specify in either code multiple criteria I have tried ands and ors with no success. I am new to this guys so any help would be appreciated. The column I need to autofilter or search is "R" and delete all rows that contain any of the four item MPD, PAI, Private Contractor or Local Council
cheers and thanks
Ron

 

[mirabeau]

Hi I have used both sets of code within the thread. This successfully deletes or autofilters and deletes one set of criteria. I have the following items that I need to deleted the whole row for.
PAI, MPD, Private Contractor, Local Council. I can succesfully delete one item at a time.

Is there a way to specify in either code multiple criteria I have tried ands and ors with no success. I am new to this guys so any help would be appreciated. The column I need to autofilter or search is "R" and delete all rows that contain any of the four item MPD, PAI, Private Contractor or Local Council
cheers and thanks
Ron

Hey Ron,
Try this one:

Sub test()
Dim x, lr As Long, lc As Integer
Dim a, b() As Variant, i As Long, e
x = Array("MPD", "PAI", "Private Contractor", "Local Council")
lr = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
lc = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, _
    searchdirection:=xlPrevious).Column
If lc < 18 Then
    MsgBox "Column R is unused" & Chr(10) & "Exiting"
    Exit Sub
End If
a = Cells(1, "r").Resize(lr)
ReDim b(1 To lr, 1 To 1)
For i = 1 To lr: For Each e In x
    If InStr(a(i, 1), e) Then b(i, 1) = 1
Next e, i
Cells(1, lc + 1).Resize(lr) = b
Cells(1, 1).Resize(lr, lc + 1).Sort Cells(1, lc + 1), 1
Cells(1, 1).Resize(Cells(1, lc + 1).End(xlDown).Row, lc + 1).Delete 3
End Sub

 

[mirabeau]

I used the VBA code supplied above from the 2nd person who responded. This works great; however, I have over 50,000 rows of data and it is taking an extremely long time to process through this macro. Is there any way to speed up the overall process of this code?

aeddipa,

with 50k rows the row by row deletion approach (EntireRow.delete etc) can be pretty slow.

If you're still active on this topic, the code in my preceding post can be easily modified for your case to do your problem much faster.

 

[kakan]

Hi
Tried the code but it deleted everything in the sheet. Thanks I will play with the code and see if I can get it to work.


cheers

Ron

 

[mirabeau]

Tried the code but it deleted everything in the sheet

I think this would only happen if none of your specified strings occur in Column R.

However I obviously overlooked that case when doing the code.

Try this modification and see if it does all OK

Sub test2()
Dim x, lr As Long, lc As Integer
Dim a, b() As Variant, i As Long, e, k As Boolean
Application.ScreenUpdating = False
x = Array("MPD", "PAI", "Private Contractor", "Local Council")
lr = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
lc = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, _
    searchdirection:=xlPrevious).Column
If lc < 18 Then
    MsgBox "Column R is unused" & Chr(10) & "Exiting"
    Exit Sub
End If
a = Cells(1, "r").Resize(lr)
ReDim b(1 To lr, 1 To 1)
For i = 1 To lr: For Each e In x
    If InStr(a(i, 1), e) > 0 Then
        b(i, 1) = 1
        k = True
    End If
Next e, i
If k = False Then Exit Sub
Cells(1, lc + 1).Resize(lr) = b
Cells(1, 1).Resize(lr, lc + 1).Sort Cells(1, lc + 1), 1
Cells(1, 1).Resize(Cells(1, lc + 1).End(xlDown).Row, lc + 1).Delete 3
Application.ScreenUpdating = True
End Sub

 

[kakan]

Hi mate

My apologies. I have been diverted by some family issues. Have got back to this and it worked fine, thank you my friend.

cheers

ron

 

[Symon]

I've found that any code for this that requires looping for this task takes a long time.

Simple but effective and also 100% recordable, I just:

Autofilter what I want to see
copy all cells
create new sheet
paste
delete original data
paste filtered data back in from your new sheet
delete the sheet you created