Delete all rows based on cell values

Question

I want to take an imported data dump in Excel (2003) and format it in the report. Most of what I did included recording a macro and then tweaking the code where necessary. I have a place that requires clean code.

I have a SORTED (D) column that lists the types of incidents (e.g. vehicle fires, bumps, animal bites, etc.). I would like to read each value in column D, and if it is NOT one of the few values ​​we are looking for, delete the entire row.

I tried several versions of the code (which I found online), and the code that produces the results closest to what I need is as follows:

Range("D:D").Select
Dim workrange As Range
Dim cell As Range
Set workrange = Intersect(Selection, ActiveSheet.UsedRange)
For Each cell In workrange
    If ActiveCell.Value <> "VFIRE" _
        And ActiveCell.Value <> "ILBURN" _
        And ActiveCell.Value <> "SMOKEA" _
        And ActiveCell.Value <> "ST3" _
        And ActiveCell.Value <> "TA1PED" _
        And ActiveCell.Value <> "UN1" _
            Then ActiveCell.EntireRow.Delete
Next cell
End Sub

(~ 100 168), , , . , "ILBURN" "SMOKEA", "ST3", . , , .

?

Answer 1

.

Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long

For RowToTest = Cells(Rows.Count, 4).End(xlUp).Row To 2 Step -1

With Cells(RowToTest, 4)
    If .Value <> "VFIRE" _
    And .Value <> "ILBURN" _
    And .Value <> "SMOKEA" _
    And .Value <> "ST3" _
    And .Value <> "TA1PED" _
    And .Value <> "UN1" _
    Then _
    Rows(RowToTest).EntireRow.Delete
End With

Next RowToTest

End Sub

Answer 2

@Tim_Williams, , , , . - .

Sub Main()
Dim workrange As Range
Dim Firstrow As Integer
Dim Lastrow As Integer
Dim lrow As Integer

'Find first and last used row in column D
Range("D:D").Select
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.Range("D1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

'Loop through used cells backwards and delete if needed
For lrow = Lastrow To Firstrow Step -1
    Set workrange = Cells(lrow, 4)
    If workrange.Value <> "VFIRE" _
        And workrange.Value <> "ILBURN" _
        And workrange.Value <> "SMOKEA" _
        And workrange.Value <> "ST3" _
        And workrange.Value <> "TA1PED" _
        And workrange.Value <> "UN1" _
            Then workrange.EntireRow.Delete

Next lrow

End Sub

Answer 3

, , 100 , 10 , " # > 10" .

Sub trim_row()

' trim_row Macro
' to trim row which is greater than iteration#10

Dim RowToTest As Long
Dim temp As String
Dim temp1 As Integer

For RowToTest = Cells(Rows.Count, 6).End(xlUp).Row To 2 Step -1

With Cells(RowToTest, 6)

temp = .Value
If temp = "" Then

    Else
    temp1 = Trim(Replace(temp, "Iteration# :: ", ""))

        If temp1 > 10 Then
        Rows(RowToTest).EntireRow.Delete
        End If
End If

End With

Next RowToTest

End Sub

The user changes and disconnects to extract the number from the line, and introduced the condition if = "" to skip empty lines. I had an empty line dividing the test data set.