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

 

[Fluff]

Hi & welcome to the board.
How about

With Range("C3", Range("G" & Rows.Count).End(xlUp))
   .AutoFilter Field:=1, Criteria1:="<>*Sales*"
   .AutoFilter 5, "Bonus"
   .Offset(1).EntireRow.Delete
   .AutoFilter
End With

 

[vcoolio]

Edit

 

[MichaelaM]

Hi there

Thanks a lot for the quick replies, but unfortunately neither worked out. I have a report with 30 columns and over 60k rows. I need to filter and delete all rows, that do not contain "Sales" in column C and "Bonus" in Column G. Rows 1-2 contain explanations, row 3 the header and basically it should start filtering and deleting from row 4.

Thanks a lot once again!

 

[Fluff]

In what way isn't it working?

 

[Jeff Brooks]

Hi there. I modified your code very slightly, but I think this should work.

Application.ScreenUpdating = False


lRow = Range("C3").End(xlDown).Row


Range("C3:G" & lRow).AutoFilter Field:=1, Criteria1:="Sales"
Range("C3:G" & lRow).AutoFilter Field:=5, Criteria1:="Bonus"




Application.ScreenUpdating = True


End Sub

 

[My Aswer Is This]

Do you mean:

all rows, that do not contain "Sales" in column C and

do not contain "Bonus" in column G​

​

 

[MichaelaM]

Hi Fluff,

After running the Macro it returned only 2 rows, whereas they should be around 3000 and something.

Hi My Aswer Is This Yes, all columns that do not contain "Sales" in column C and do not contain "Bonus" in column G need to be deleted (not just filtered).

 

[Fluff]

In that case try

   Dim Usdrws As Long
   
   Usdrws = Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Application.ScreenUpdating = False
   With Range("C3:G" & Usdrws)
      .AutoFilter 1, "<>*Sales*"
      .AutoFilter 5, "<>*Bonus*"
      .Offset(1).EntireRow.Delete
      .AutoFilter
   End With
   Application.ScreenUpdating = True

 

[MichaelaM]

Hi Fluff,

This code deleted everything but the header.