Hello folks:
I want to delete all rows in Column R that does not contain the word "closed" and all rows in Column S that do not contain the word Complete.
In other words, I want my spreadsheet to just contain the rows where Closed and Complete are next to each other, everything else can be completed.
The code below works partially, If R contains Closed, it will not delete S were Complete is not found.
Where am I going wrong here?
I want to delete all rows in Column R that does not contain the word "closed" and all rows in Column S that do not contain the word Complete.
In other words, I want my spreadsheet to just contain the rows where Closed and Complete are next to each other, everything else can be completed.
The code below works partially, If R contains Closed, it will not delete S were Complete is not found.
Where am I going wrong here?
Code:
Sub filter_Issues_Ticks()
Dim LastRow As Long
Sheets("Issues").Select
LastRow = Range("R:S").Find("", , , , 1, 2).Row
Application.ScreenUpdating = False
Range("R1:S" & LastRow).AutoFilter Field:=1, Criteria1:="<>Closed"
Range("R1:S" & LastRow).AutoFilter Field:=2, Criteria1:="<>Complete"
If Range("R1:S" & LastRow).SpecialCells(xlCellTypeVisible).Count > 2 Then
Range("R2:S" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub