Delete row if cell has a specific value

Question

I am trying delete a row if a cell has a specific value.

Sub DeleteRows()
    '
    ' DeleteRows Macro

    Dim Rng As Range
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Set Rng = ActiveSheet.UsedRange
    For i = Rng.Cells.Count To 1 Step -1
        If Rng.Item(i).Value <> "M of E" Then
            Rng.Item(i).EntireRow.Delete
        End If
    Next i
    
End Sub

I am selecting whole range in column B and trying to say if the value in the cell is not the phrase M of E, then the row should be deleted.

It deletes everything, even if the value of the cell is M of E.

If I change the <> operator in the code to an = operator, the code works, only deleting rows that have whatever text I specify.

I cannot figure out why the not equal operator isn't working.

`Set Rng = ActiveSheet.UsedRange`... try `Debug.Print Rng.Address` and see what the result is. — BigBen, May 20 '20 at 16:02

score 0 · Accepted Answer · answered May 20 '20 at 22:20

I will identify what is wrong in your code and then show you what it should be.

First: don't use select, see This SO Question

Range("B1").Select

Second: using End(xlDown) searches down until the last used cell before the first blank cell, if there is a blank cell before the last filled cell, you will not select the whole used range.

Range(Selection, Selection.End(xlDown)).Select

Third: setting your range variable to UsedRange, selects every used cell, negating the range in Column B; e.g. If your used range was Range("A1:F20"), then Rng = Range("A1:F20")

Set Rng = ActiveSheet.UsedRange

Forth: you start your loop from the last cell in Rng to the first cell in Rng; e.g. using the above example, your loop would start at Range("F20") and the next cell in the loop would be Range("E20"), etc.

For i = Rng.Cells.Count To 1 Step -1

Fifth: Your If statement checks for the text in each cell of Rng. Thus, it will check Range("F20") first, and since your text is not in a cell until Column B. So, the first and subsequent cells will be True for <> and the row will be deleted. The loop will still check every cell left in the row and delete the row again-and-again, and then start the next row with the same outcome. etc.

If Rng.Item(i).Value <> "M of E" Then 
    Rng.Item(i).EntireRow.Delete
End If

Below is basic code to loop through each cell in column B, and delete the rows that don't have "M of E",

Sub DeleteRows()
' DeleteRows Macro

'you should always set the workbook and worksheet
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")

'assign a last row variable using .End(xlUp).Row
Dim lRow As Long: lRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

    'loop from the last row to the second row(skip header)
    For i = lRow To 2 Step -1
        'use Cells to set the row and column and check for "M of E".
        If ws.Cells(i, "B").Value <> "M of E" Then
            'If the cell in Column B doesn't contain "M of E", delete the row.
            ws.Cells(i, "B").EntireRow.Delete
        End If
    Next i 'loop
End Sub

Delete row if cell has a specific value

1 Answers1