Efficient way to delete rows (Multiple criterias) VBA

Question

I have a sheet where in Col A there is a String A and Col B consists of String B.

I want to keep rows with the Word 'Begründung' in Col A and 'Nein' in Col B.

I am using the following code found from these sources Efficient way to delete entire row if cell doesn't contain '@' & Delete Row based on Search Key VBA

Sub KeepOnlyAtSymbolRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("A1:A" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>*Begründung*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False
End Sub

I want to add another criteria for the Col B. Wherever in Col B 'Nein' is encountered that row is kept and the rest is deleted but at the same time 'Begrüundung in Col A is kept and the rest is deleted.

In other words wherever the words 'Begründung' and 'Nein' is encountered in the sheet those rows are kept and the rest is deleted.

I would really be grateful if any of you could help.

So, in order the row to be kept, both conditions must be true: ColA contains "Begrundung" and ColB contains "Nein". Is that correct? — Ron Rosenfeld
– Ron Rosenfeld, Commented Nov 3, 2014 at 12:23
@RonRosenfeld No not really. Begründung OR Nein. i provide some examples below to make it more clear e.g. Cell A1 consists of Begründung, B1 consists of Ja - Keep the Row Cell A2 Consists of Prüfkriterien, B2 Consists of Nein-Keep the Row Cell A3 Consists of Begründung and B3 consists of Nein- Keep the Row Cell A4 Consists of Prüfkriterien and B4 Consists Nein- Delete the Row — wildradical
– wildradical, Commented Nov 3, 2014 at 12:26
"wherever the words 'Begründung' and 'Nein' is encountered in the sheet those rows are kept" ... "B4 Consists Nein- Delete the Row". Is this correct? — gembird
– gembird, Commented Nov 3, 2014 at 12:35
If the AutoFilter does not retain all the rows to be deleted, then try the Advanced Filter, where you can "OR" criteria. You could also use that to copy the results to a new range, if you want. Once you have that working, record a macro. — Ron Rosenfeld
– Ron Rosenfeld, Commented Nov 3, 2014 at 12:48

score 2 · Accepted Answer · 2014-11-03 11:37:56Z

2

Try this:

Sub DeleteWithMultipleColumnsCriterias()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("A1:B" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>*Begründung*"
        .AutoFilter Field:=2, Criteria1:="<>*Nein*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False
End Sub

edited Nov 3, 2014 at 11:37

answered Nov 3, 2014 at 11:06

user2140173

Sign up to request clarification or add additional context in comments.

7 Comments

user2140173 Over a year ago

comment out this '.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete and 'ws.AutoFilterMode = False and see what the filter looks like after running the code

wildradical Over a year ago

@vba4all- All of it gets filtered completely, doesnt really help. I really appreciate the time you invest for my queries. Thank you very much in advance

Paresh J Over a year ago

@wildradical : Check this URL for multiple criteria: stackoverflow.com/questions/18135144/…

wildradical Over a year ago

@PareshJadhav- Thank you very much although the multiple criteria that is defined is for a single column. I am looking for Multiple criterias for Multiple columns. For e.g. Begründung is encoutered in Col A i.e. Col 1 and Nein is encountered in Col B i.e. Col 2.

Ron Rosenfeld Over a year ago

@wildradical How about posting an example where vba4all's code fails to work properly.

|

Collectives™ on Stack Overflow

Efficient way to delete rows (Multiple criterias) VBA

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related