Delete the entire row if it's column has specific value

[An Quala]

Hello Mr Excel Community, can anyone please help me to write a code which deletes the entire rows if column AM contains a value which is in sheet "Control Panel" Cell S7, it will be the phrase match, so for example if cell S7 has value "Apple" then it should delete the row with the value of "Apple Juice" in Column AM.

Thank you.

 

[EXCEL MAX]

You can try this one

Sub DeleteEntireRows()
   
    With ActiveSheet
        Set vRng = .UsedRange.Columns("AM")
        vRngS7 = Sheets("Control Panel").Range("S7").Value
        vRows = vRng.Rows.Count
        For vN = vRows To 1 Step -1
            If InStr(1, UCase(.Cells(vN, "AM")), UCase(vRngS7)) Then _
                .Rows(vN).EntireRow.Delete
        Next vN
    End With

End Sub

 

[Joe4]

To make it go a little faster (and get rid of screen flickering), you can suppress screen updating until the end by adding the following lines in to the previous code, i.e.

Sub DeleteEntireRows()

    Application.ScreenUpdating = False

    With ActiveSheet
        Set vRng = .UsedRange.Columns("AM")
        vRngS7 = Sheets("Control Panel").Range("S7").Value
        vRows = vRng.Rows.Count
        For vN = vRows To 1 Step -1
            If InStr(1, UCase(.Cells(vN, "AM")), UCase(vRngS7)) Then _
                .Rows(vN).EntireRow.Delete
        Next vN
    End With

    Application.ScreenUpdating = True

End Sub

If you have a LOT of rows, the difference in time could be a bit noticeable.

 

[An Quala]

Hello @EXCEL MAX @Joe4 I have changed the range to this,

Application.ScreenUpdating = False

    With ActiveSheet
        Set vRng = .UsedRange.Columns("AQ")
        vRngJ42 = Sheets("Control Panel").Range("J42").Value
        vRows = vRng.Rows.Count
        For vN = vRows To 1 Step -1
            If InStr(1, UCase(.Cells(vN, "AQ")), UCase(vRngJ42)) Then _
                .Rows(vN).EntireRow.Delete
        Next vN
    End With

    Application.ScreenUpdating = True

But it is continuously looping around For and Next in this position but not going forward, can you please check what is the issue?

​

 

[bebo021999]

to help code faster, try auto filter then delete rows at once

Option Explicit
Sub test()
Dim vRng&, vRngJ42
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
    vRng = Cells(Rows.Count, "AQ").End(xlUp).Row ' last used row of column AQ
    vRngJ42 = Sheets("Control Panel").Range("J42").Value
    With Range("AQ2:AQ" & vRng)
        .AutoFilter field:=1, Criteria1:="*" & vRngJ42 & "*" ' set auto filter for column AQ, from cell AQ2
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete ' delete rows with criteria match
    End With
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

 

[An Quala]

Thank you, let me try this, but as this is going to be a part of another code, can you please remove the "option explicit" part?

 

[bebo021999]

Thank you, let me try this, but as this is going to be a part of another code, can you please remove the "option explicit" part?

Sure. I ave just taken a look to your other thread with full code. I hope autofilter idea will help a lot.

 

[EXCEL MAX]

My mistake with setting used range column.

Sub DeleteEntireRows()
   
    Application.ScreenUpdating = False
    With ActiveSheet
       'Set vRng = .UsedRange.Columns("AQ")
        Set vrng = .Range("AQ1", Cells(Rows.Count, "AQ").End(xlUp))
        vRngJ42 = Sheets("Control Panel").Range("J42").Value
        vRows = vrng.Rows.Count
        For vN = vRows To 1 Step -1
            If InStr(1, UCase(.Cells(vN, "AQ")), UCase(vRngJ42)) Then _
                .Rows(vN).EntireRow.Delete
        Next vN
    End With
    Application.ScreenUpdating = True
    
End Sub

 

[An Quala]

Thank you @EXCEL MAX Can you please add one more thing in this, instead of just taking the value from J42, it should take the values from the range J42:J46, and if any of the value of this range comes in the Column AQ, it should delete the rows as always, thank you!

 

[EXCEL MAX]

Thank you for feedback.
Here is extended version with "For Each" loop.

Sub DeleteEntireRows()

    Dim vRng As Range, vRngJ42 As Range, vRows As Long, _
        vN As Long, vR As Range
   
    Application.ScreenUpdating = False
    With ActiveSheet
        Set vRng = .Range("AQ1", Cells(Rows.Count, "AQ").End(xlUp))
        Set vRngJ42 = Sheets("Control Panel").Range("J42:J46")
        vRows = vRng.Rows.Count
        For Each vR In vRng
            For vN = vRows To 1 Step -1
                If InStr(1, UCase(.Cells(vN, "AQ")), UCase(vR)) Then _
                    .Rows(vN).EntireRow.Delete
            Next vN
        Next vR
    End With
    Application.ScreenUpdating = True
   
End Sub