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]

Try changing the delete line to

      .Offset(1).SpecialCells(xlVisible).EntireRow.Delete

If that doesn't work, can you post some sample data.
There are tolls available here https://www.mrexcel.com/forum/about-board/508133-attachments.html
to help you do that & a test board here, where you can try them out

 

[vcoolio]

Hello MichaelaM,

With Fluff's code in post #9 , try the code slightly modified as follows:-

   Application.ScreenUpdating = False

   With Sheet1.[A3].CurrentRegion
      .AutoFilter [COLOR=#ff0000]3[/COLOR], "<>Sales"
      .AutoFilter [COLOR=#ff0000]7[/COLOR], "<>Bonus"
      .Offset(1).EntireRow.Delete
      .AutoFilter
   End With

   Application.ScreenUpdating = True

I hope that this helps.

Cheerio,
vcoolio.

 

[Fluff]

@vcoolio
From what the OP said in post#4, that's going to put the filter in row1 not row3

 

[Jeff Brooks]

Hello Again,
I took a slightly different approach and didn't filter at all. Instead, the following code works backward from your very last row all the up to row 3 and deletes any rows that don't match both criteria ("Sales" in column 3 and "Bonus" in column 5). I certainly hope this works, but perhaps one of the previous methods already work too.


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

For iCntr = lRow To 3 Step -1
If (Cells(iCntr, 3) = "Sales" And Cells(iCntr, 7) = "Bonus") Then
'Do Nothing

Else
'Delete Row
Rows(iCntr).Delete

End If

Next


End Sub

 

[Jeff Brooks]

Sorry I meant "Bonus" in column 7 ​(not 5)

 

[MichaelaM]

Hi Jeff,

Your code in post 4 is filtering correctly, just need it to delete the remaining ones. Can you do that somehow? The code in post 14, tried that already but it's too slow.

 

[My Aswer Is This]

edit

 

[MichaelaM]

Hi My Aswer Is This thanks 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.

 

[Jeff Brooks]

You could just paste the filtered data into a new sheet (e.g., Sheet2, etc.) in your workbook.


Sub DeleteRows()


Application.ScreenUpdating = False


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


'Filter to Conditions you want
Range("C3:G" & lRow).AutoFilter Field:=1, Criteria1:="Sales"
Range("C3:G" & lRow).AutoFilter Field:=5, Criteria1:="Bonus"

'Copy the filtered columns to a new sheet
Columns("A:G").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste




Application.ScreenUpdating = True


End Sub

 

[My Aswer Is This]

It appears as if using two does not equal filters is a hard thing to do.