Deleting rows based on 2 criteria

MichaelaM

New Member
Joined
Apr 2, 2018
Messages
9
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hello MichaelaM,

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

Code:
   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.
 
Upvote 0
@vcoolio
From what the OP said in post#4, that's going to put the filter in row1 not row3
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top