Delete all rows except the ones with a specific value-CodePudding

How, based on column values can I keep certain rows and delete the rest? Is it possible to delete everything except the value I want to keep?

Right now I repeat the values I want to delete, but this is not the best solution. Do you think it is possible to delete everything except the value I want to keep?

I would really appreciate your help. I am a beginner.

With ActiveSheet
    FirstRow = 4
    LastRow = 10000

    For Row = LastRow To FirstRow Step -1
        If .Range("A" & Row).Value = "ITT1" Then
            .Range("A" & Row).EntireRow.Delete
        End If
    Next Row
End With

CodePudding user response：

You need to negate your If statement using <> or Not:

Just use

If .Range("A" & Row).Value <> "ITT1" Then

If Not .Range("A" & Row).Value = "ITT1" Then

to delete all rows except the ones with ITT1 as value in column A.

Note that instead of hard coding the last row LastRow = 10000 you can determine it using something like:

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row  ' get last used row in column A

So if you only have 100 rows the loop does not start at 10000 but just loops over the rows that actually have data.

So It should look something like:

With ActiveSheet
    Const FirstRow As Long = 4

    Dim LastRow As Long
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row  ' get last used row in column A

    Dim Row As Long
    For Row = LastRow To FirstRow Step - 1
        If Not .Range("A" & Row).Value = "ITT1" Then
            .Range("A" & Row).EntireRow.Delete
        End If
    Next Row
End With

If it has to delete a lot of rows this might get a bit slow. So I recommend to collect the rows to delete in a variable using Union and delete them all at once in the end, which is much faster:

With ActiveSheet
    Const FirstRow As Long = 4

    Dim LastRow As Long
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row  ' get last used row in column A

    Dim RowsToDelete As Range  ' here we collect which rows to delete

    Dim Row As Long
    For Row = LastRow To FirstRow Step - 1
        If Not .Range("A" & Row).Value = "ITT1" Then
            If RowsToDelete Is Nothing Then
                ' set first row
                Set RowsToDelete = .Range("A" & Row).EntireRow
            Else
                ' add all the other rows
                Set RowsToDelete = Application.Union(RowsToDelete, .Range("A" & Row).EntireRow)
            End If
        End If
    Next Row

    If Not RowsToDelete Is Nothing Then
        RowsToDelete.Delete
    Else
        MsgBox "No rows to delete were found."
    End If
End With

If you collect all rows to delete in a variable first you even don't need to run the loop backwards Step - 1 and just use a normal forward loop if you like:

For Row = FirstRow To LastRow