Excel VBA: Delete rows if value not equal to a value?

Question

So I've been searching hard to find why my code hasn't been working, but every time I try, I get a result where nothing is changed. Can someone please tell me what I'm missing? Sorry, I'm a total novice but I'm trying.

   Dim Cell As Range

With Sheets(1)
    ' loop column D until last cell with value (not entire column)
    For Each Cell In .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
        If Cell.Value <> 110 Then
            Rows(Cell.Row).EntireRow.Delete
        End If
    Next Cell
End With

2 things. If you're deleting rows, you need to go backwards through the collection. Also, you need to fully qualify `Rows(Cell.Row).EntireRow.Delete`. — Comintern, Nov 21 '18 at 20:01
[this might be of use](https://stackoverflow.com/questions/24358183/reverse-order-of-for-each-loop) — Jchang43, Nov 21 '18 at 20:07
Also: https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 — Tim Williams, Nov 21 '18 at 20:10
I'm reading through the posts and working on figuring it out, but thank you all so much for your help! This goes a long way! — El Asado, Nov 21 '18 at 20:14

Reafidy · Accepted Answer · 2018-11-26T20:24:12.547

3

Instead of looping, make use of excels inbuilt functions, its cleaner and more concise.

With Sheets(1).UsedRange
    .AutoFilter Field:=4, Criteria1:="<>110"
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With

if you insist on looping then use the following code:

With Sheets(1).UsedRange
    For lrow = .Rows.Count To 2 Step -1
        If .Cells(lrow, 4).Value <> 110 Then .Rows(lrow).Delete
    Next lrow
End With

edited Nov 26 '18 at 20:24

answered Nov 21 '18 at 20:16

Reafidy

8,240
5
51
83

THANK YOU SO MUCH! Yes, I'll research more into the inbuilt functions to get better, but both of these codes worked perfectly, thank you. – El Asado Nov 21 '18 at 20:34
@ElAsado, just a heads up. You might need to change `"=110"` to `"<>110"` here, as your title and original post suggests you want to delete the rows where column D does not equal 110. But other than that, you're good I think. – chillin Nov 21 '18 at 20:48
@Reafidy: You probably haven't noticed, the first solution deletes row 1 regardless of the content. Know how to fix it? – VBasic2008 Nov 22 '18 at 07:08

score 1 · Answer 2 · answered Nov 21 '18 at 20:26

Untested, but maybe something like:

Option explicit

Sub DeleteRows()

With thisworkbook.worksheets(1)
    ' loop column D until last cell with value (not entire column)

Dim lastRow as long
lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

Dim rowIndex as long
For rowIndex = lastRow to 2 step -1

If .cells(rowIndex, "D").value2 <> 110 then
.cells(rowIndex, "D").entirerow.delete
End if

Next rowIndex

End With

End sub

If you have a lot of rows, you could use union to build a range consisting of all rows to be deleted, then delete them in one go.

Excel VBA: Delete rows if value not equal to a value?

2 Answers2

Linked

Related