Deleting rows based on 2 criteria

[MichaelaM]

Hi,

I am totally new with VBA and was trying to make a macro deleting rows based on certain criteria. With one criteria it worked just fine, but I am struggling to add a second criteria. In short, below the code that I have so far. I need it to delete all rows that do not contain "Sales" in column C and "Bonus" in column G. Could anyone help?

Application.ScreenUpdating = False
With Range("C3", Range("C" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="<>*Sales*"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True

 

[vcoolio]

@Fluff:-

I've used the CurrentRegion method so the actual field(column) number needs to be used: 3(C) and 7(G).

Using the column range

Range("C3", Range("G" & Rows.Count).End(xlUp))

Column C is field number 1 and Column G is field number 5 (as per your posts nos. 2 and 9).

Here's a little sample, more so for the OP's sake:-

http://ge.tt/3GejDHp2

Cheerio,
vcoolio.

 

[My Aswer Is This]

Hi My Aswer Is Thisthanks a lot for your help it's close, but still filtering and deleting only other than "Sales" in Column C. "Bonus" in G remains the same nothing filtered or deleted.

Yes I was able to get one column like C to filter OK but not column G to filter out.

This seems to be a difficult thing to do using does not equal on two different fields.
I will be watching to see what the answer to this is?

 

[My Aswer Is This]

Try this:

Sub Filter_Me()
Application.ScreenUpdating = False
'Modified 4-3-18 5:25 AM EST
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
        With Range(Cells(1, "C"), Cells(Lastrow, "G"))
            .AutoFilter 1, "<>" & "Sales"
            .AutoFilter 5, "<>" & "Bonus"
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
          .AutoFilter
        End With

Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Sorry. But my previous post does not work like you wanted.

 

[MichaelaM]

This didn't work either, it deleted half of the rows, but not all. In the meantime I have solved my problem in a less elegant way, but still effective. I just recorded a macro :D Thanks a lot for all the help! Much appreciated.

 

[My Aswer Is This]

This didn't work either, it deleted half of the rows, but not all. In the meantime I have solved my problem in a less elegant way, but still effective. I just recorded a macro :D Thanks a lot for all the help! Much appreciated.

I never like giving up. There must be a way to do this.

 

[MichaelaM]

Yes you are right, but I spent the last 2 days trying to find a solution and all i got is headache :D That's what the code looks like after I recorded the steps, maybe this could help to find a more elegant solution. I have just changed the actual text for simplify.

Range("A3").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$AD$55357").AutoFilter Field:=3, Criteria1:=Array( _
"text to be deleted", "text1 to be deleted”, "text2 to be deleted", "text3 to be deleted”
Operator:=xlFilterValues
Rows("1129:1129").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$3:$AD$2231").AutoFilter Field:=3
ActiveSheet.Range("$A$3:$AD$2231").AutoFilter Field:=7, Criteria1:="=text4 to be deleted", _
Operator:=xlOr, Criteria2:="=text5 to be deleted"
Rows("1413:1413").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$3:$AD$1412").AutoFilter Field:=7
Selection.AutoFilter

 

[My Aswer Is This]

I can write a script to loop through the range and delete rows if Column "C" does not equals "Sales" and Column "G" does not equals Bonus but people on this forum always say loops are too slow.
I was surprised no one had a way to do this with a filter.

Would you like a looping script to do this.
Now what slow means is another story.
Some people believe 2.5 milliseconds is slow.
I never test my scripts for speed. I would say any thing less the a minute would be fast if we are doing 100,000 rows.

 

[MichaelaM]

We can try, thanks a lot for trying to help me! I tried at the beginning something that may have been a loop (like I said, I am very new to all this) and it took maybe 10 minutes. So if it doesn't take 10 minutes and works I'll be more than happy