Delete rows based on two conditions

[jmazorra]

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?

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

 

[VoG]

Try

Sub TEST()
Dim LR As Long, I As Long
LR = Range("R" & Rows.Count).End(xlUp).Row
For I = LR To 1 Step -1
    With Range("R" & I)
        If LCase(.Value) <> "closed" And LCase(.Offset(, 1).Value) <> "complete" Then .EntireRow.Delete
    End With
Next I
End Sub

 

[ClimoC]

I never use filters in code. There's a difference between something being hidden and something being not there. When you filter, you just hide those rows. The cells (or Range, as you've used) don't go anywhere.

Use this instead, will save you a lot of bother

Sub Filter_Issues_Ticks()




Application.ScreenUpdating = False
With Sheets("Issues")
For i = .Range("R65000").End(xlUp).Row To 2 Step -1
If .Cells(i, 18).Value <> "Closed" Then
        If .Cells(i, 19).Value <> "Complete" Then
                .Cells(i, 1).EntireRow.Delete shift:=xlUp
                
        Else
              If .Cells(i, 18).Value <> "Closed" Then .Cells(i, 1).EntireRow.Delete shift:=xlUp
        End If
Else
            If .Cells(i, 19).Value <> "Complete" Then .Cells(i, 1).EntireRow.Delete shift:=xlUp
End If
Next
End With
Application.ScreenUpdating = True


End Sub

 

[ClimoC]

Try

Sub TEST()
Dim LR As Long, I As Long
LR = Range("R" & Rows.Count).End(xlUp).Row
For I = LR To 1 Step -1
    With Range("R" & I)
        If LCase(.Value) <> "closed" And LCase(.Offset(, 1).Value) <> "complete" Then .EntireRow.Delete
    End With
Next I
End Sub

This was the first thing I wrote - it didn't work, it doesn't seem to evaluate the two together... do we need parenthesis around the two conditions of the if statement VoG?

 

[VoG]

ADVERTISEMENT

This was the first thing I wrote - it didn't work, it doesn't seem to evaluate the two together... do we need parenthesis around the two conditions of the if statement VoG?

I just tested it and it worked (assuming that I understood the requirements).

 

[ClimoC]

Huh. Didn't for me... if col2 = "Complete" and Col1 <> "Closed", it wouldn't delete my row

 

[James Snyder]

Try a single AutoFilter:

    Range("R1:S" & LastRow).AutoFilter Field:=1, Criteria1:="<>Closed", _
        Operator:=xlAnd, Field:=2, Criteria2:="<>Complete"

From the web:

With Sheet1
              .AutoFilterMode = False
              .Range("A1:D1").AutoFilter
              .Range("A1:D1").AutoFilter Field:=2, Criteria1:=">=35", _
               Operator:=xlAnd, Criteria2:="<=45"      
End With

EDIT: note that the field numbers should match your columns. I did not change that...

 

[jmazorra]

Thanks for the feedback,