Advice on delete row VBA code

[VBA learner ITG]

Hi Peers,

Can i get your advice to see if there is a quicker way of deleting rows than the piece of code that i wrote.

Sheets("sheet1").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$1:$158").AutoFilter Field:=4, Criteria1:="=MIXED SEL 2" _
, Operator:=xlOr, Criteria2:="=MIXED SEL 4"
ActiveSheet.Range("$1:$158").AutoFilter Field:=3, Criteria1:="B"
Rows("13:13").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.DELETE Shift:=xlUp
Range("I154").Select
ActiveSheet.Range("$1:$150").AutoFilter Field:=4
ActiveWindow.SmallScroll Down:=45
Range("D73").Select
ActiveWindow.SmallScroll Down:=-57

 

[mumps]

It might be easier to help if you could provide a detailed explanation in words of what you want to do. It would also help if you could attach a screen shot of what your data looks like.

 

[VBA learner ITG]

Hi Mumps,

Thank you for coming back to me:

On Sheet1
Criteria 1 = Column D if contains text "MIXED SEL 2" OR / AND "MIXED SEL 4"
Criteria 2 = Column C if contains text "B"
Delete all rows based on the above criterias.

 

[mumps]

If column C contains "B", does column D also have to contain "MIXED SEL 2" OR / AND "MIXED SEL 4" in the same row as the "B" in order for the row to be deleted?

 

[VBA learner ITG]

Hi Mumps,

Thank you for coming back to me: Yes thats correct

"Column C contains "B", does column D also have to contain "MIXED SEL 2" OR / AND "MIXED SEL 4" in the same row as the "B"

 

[mumps]

The macro assumes that you have headers in row 1 and that your data starts in row 2.

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("C1:D" & LastRow).AutoFilter Field:=2, Criteria1:="=MIXED SEL 2", Operator:=xlOr, Criteria2:="=MIXED SEL 4"
    Range("C1:D" & LastRow).AutoFilter Field:=1, Criteria1:="B"
    Range("C2:D" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[VBA learner ITG]

Thank you Mumps for you help on tidying up my code and providing a new logical approach to trackling this issue in the future.

Your code ran much faster than my written code.

Thank you

 

[mumps]

You are very welcome.