Hey All,
I'm not sure how to adjust the line of code below where it deletes rows that match my criteria when filtering. There are times when I do not have records with "delete" and so all rows on the spreadsheet are deleted.
Any suggestions?
Sub filtertest()
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(COUNTIF(C[-11],RC[-11])=2,ISNA(RC[-7])),""delete"","""")"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N" & Range("A" & Rows.Count).End(xlUp).Row)
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("A1:N1").AutoFilter Field:=14, _
Criteria1:=Array("delete"), _
Operator:=xlFilterValues
With ActiveSheet.AutoFilter.Range
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
End With
ActiveSheet.ShowAllData
End Sub
I'm not sure how to adjust the line of code below where it deletes rows that match my criteria when filtering. There are times when I do not have records with "delete" and so all rows on the spreadsheet are deleted.
Any suggestions?
Sub filtertest()
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(COUNTIF(C[-11],RC[-11])=2,ISNA(RC[-7])),""delete"","""")"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N" & Range("A" & Rows.Count).End(xlUp).Row)
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("A1:N1").AutoFilter Field:=14, _
Criteria1:=Array("delete"), _
Operator:=xlFilterValues
With ActiveSheet.AutoFilter.Range
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
End With
ActiveSheet.ShowAllData
End Sub