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, Nov 03 '14 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, Nov 03 '14 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? — Daniel Dušek, Nov 03 '14 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, Nov 03 '14 at 12:48

score 2 · Accepted Answer · 2014-11-03T11:37:56.490

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 03 '14 at 11:37

answered Nov 03 '14 at 11:06

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 – Nov 03 '14 at 11:11
@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 – wildradical Nov 03 '14 at 11:16
@wildradical : Check this URL for multiple criteria: http://stackoverflow.com/questions/18135144/vba-autofilter-with-multiple-criteria-using-variables – Paresh J Nov 03 '14 at 11:30
@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. – wildradical Nov 03 '14 at 11:57
1

@wildradical How about posting an example where vba4all's code fails to work properly. – Ron Rosenfeld Nov 03 '14 at 13:00
@RonRosenfeld The edited code worked pretty well. Thank you very much for the efforts and the time you all invested. Cheers! – wildradical Nov 03 '14 at 13:18
@vba4all- Thank you very much! I am pretty novice and a bit curious and have a couple of questions. Do answer if you have time otherwise its ok as well. 1) can you please tell me what difference this '<>' actually makes? 2) the range defined by you based on my column criteria now is Set rng = ws.Range("A1:B" & lastRow) What if my present col B is actually Col F. Can i set the range as Set rng = ws.Range("A1:F" & lastRow) Thank you very much in advance! – wildradical Nov 03 '14 at 13:23

Efficient way to delete rows (Multiple criterias) VBA

1 Answers1

Linked