Removing rows with value different to a list

Question

I have 4 excel files with around 2000 rows each. In A column, I have several 4 characters strings.

I want to remove all the rows except the ones with codes starting with:

E, L, GC, UD, UG, UB, UK, B

There are too many for filtering one by one, I guess a macro would be a better solution.

My code is this one, but it is just leaving the B values instead of all the interesting ones:

ActiveSheet.Range("$A$1:$A$1379").AutoFilter Field:=1, Criteria1:=Array("=E*", _
    "=GC*", "=UD*", _
    "=UG*", "=UB*", _
    "=UK*", "=B*"), _
    Operator:=xlOr

How would you do it?

Please post any code you have tried thusfar; Stack Overflow is a collaborative website, not a code-for-you service. If you need help getting started, use the Macro Recorder in the Developer tab of Excel. Regarding the loop aspect, look into dynamic last row, For loops, and/or For Each loops. — Cyril, Sep 27 '17 at 14:31
Sorry, I edited with the code now. My approach is a normal filter with: starting with, but it just allows 2 parameters apparently. — Daniel Viaño, Sep 27 '17 at 15:12
Use any of the solutions provided here to delete the rows you don't want: https://stackoverflow.com/questions/36873359/fastest-way-to-delete-rows-which-cannot-be-grabbed-with-specialcells — Ralph, Sep 27 '17 at 16:02
@DanielViaño Just to be sure, you want to delete rows, not just filter? Both mine and MGae2M's responses delete. Your code just autofilters. With that in mind, it might be best to also copy the content to a new sheet, then doe the deletion, as to not affect your source data. — Cyril, Sep 27 '17 at 16:38

Cyril · Accepted Answer · 2017-10-03T19:30:51.160

You can use a For loop to go through the rows, and an If statement to determine which Cells meet the criteria. This will be a long string for the if statement, but should work:

Dim i as Long, LR as Long
LR = cells(rows.count,1).End(xlUp).Row
For i = 2 to LR
    If Left(Cells(i,1).Value,1) = "E" OR Left(Cells(i,1).Value,1) = "L" OR Left(Cells(i,1).Value,2) = "GC" OR Left(Cells(i,1).Value,2) = "UD" OR Left(Cells(i,1).Value,2) = "UG" OR Left(Cells(i,1).Value,2) = "UB" OR Left(Cells(i,1).Value,2) = "UK" OR Left(Cells(i,1).Value,1) = "B" Then
        'Nothing
    Else
        Rows(i).Delete
    End If
Next i

All of the work is in that If-statement. You could make it If Not Left(...), if you wanted to forgo the Else part. This assumes you are working in Column 1 (A).

Edit

Per the second comment I left on the post, try working in a new sheet with the .Delete code:

Sheets("SOURCENAME").Copy Sheets(Sheets.Count)

You then would run the loop on the newly created sheet

Edit2:

When deleting cells, you want to go backwards such that:

For i = LR to 2 Step -1
    'Stuff
Next i

The issue now is that when I delete the cell, range changes and so loop is missing data. If Cell A49 is deleted then the value at A50 moves up to A49 and is never checked, so I am keeping some unwanted columns — Daniel Viaño, Oct 03 '17 at 18:35
@DanielViaño Check Edit2... when deleting you want to specify that you are stepping throught he loop in the negative direction, such that LR to 2 Step -1. — Cyril, Oct 03 '17 at 19:31

mgae2m · Answer 2 · 2017-10-03T17:42:58.170

Run below in each Sheet that you need check above

Sub RemoveRows()

    Dim sh As Long
    Dim i As Long

    sh = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To sh

    ' Checking criteria section:        
    If InStr(1, Cells(i, "A").Value, "E") = 1 Then Exit Sub
    If InStr(1, Cells(i, "A").Value, "L") = 1 Then Exit Sub
    If InStr(1, Cells(i, "A").Value, "GC") = 1 Then Exit Sub
    If InStr(1, Cells(i, "A").Value, "UD") = 1 Then Exit Sub
    If InStr(1, Cells(i, "A").Value, "UG") = 1 Then Exit Sub
    If InStr(1, Cells(i, "A").Value, "UB") = 1 Then Exit Sub
    If InStr(1, Cells(i, "A").Value, "UK") = 1 Then Exit Sub
    If InStr(1, Cells(i, "A").Value, "B") = 1 Then Exit Sub

    Cells(i, "A").EntireRow.Delete

End Sub

Regard.

Removing rows with value different to a list

2 Answers2