Delete entire row based on multiple conditions

Mange

New Member
Joined
Sep 8, 2016
Messages
20
Hi all

I want to code the following: the macro has to delete the entire row if the value of the cell in column AR is "ABC", "DEF", "GHI" or "JKL".

My code deletes only the rows containing ABC and GHI..

Could anybody see what's wrong in the code?

Code:
Dim i As Range
Dim wsTest As Worksheet
Dim wbDatabase As Workbook
Set wbDatabase = ThisWorkbook
Set wsTest = wbDatabase.Sheets("Overview")

'Delete the information containing ABC, DEF, GHI and JKL.

For Each i In wbDatabase.Sheets("Overview").Range("AR1:AR1000")
If i.Value = "ABC" Or i.Value = "DEF" Or i.Value = "GHI" Or i.Value = "JKL" Then i.EntireRow.Delete

Next i

End Sub
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
When deleting rows, it is best to work through the range backwards, as deleting rows shifts rows up, as you are moving down the range. So consecutive entries may be missed.
Try this variation:
Code:
Sub MyDeleteMacro()
'Delete the information containing ABC, DEF, GHI and JKL.

    Dim LastRow As Long
    Dim myRow As Long
    Dim myValue As String
    Dim wsTest As Worksheet
    Dim wbDatabase As Workbook
    Set wbDatabase = ThisWorkbook
    Set wsTest = wbDatabase.Sheets("Overview")

'   Find last row in column AR
    LastRow = wbDatabase.Sheets("Overview").Cells(Rows.Count, "AR").End(xlUp).Row
    
'   Loop through rows backwards
    For myRow = LastRow To 1 Step -1
        myValue = wbDatabase.Sheets("Overview").Cells(myRow, "AR")
        If myValue = "ABC" Or myValue = "DEF" Or myValue = "GHI" Or myValue = "JKL" Then wbDatabase.Sheets("Overview").Rows(myRow).Delete
    Next myRow

End Sub
 
Upvote 0
You are welcome!
Glad I could help!:)
 
Upvote 0

Forum statistics

Threads
1,223,060
Messages
6,169,861
Members
452,286
Latest member
noclue2000

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