PDA

View Full Version : [SOLVED] Delete filtered rows - 2 conditions



roxnoxsox
06-17-2016, 06:33 AM
Hi all,

Sorry I'm just getting a bit tangled in this VBA coding so any help is much appreciated. I have a long list of numbers in column A. I'm trying to write a macro which will delete all rows that do not contain 87036 or 120317 in column A. However, I can't seem to make this work? This is coming up with an error message but I can't see what I'm missing

This is the code I am currently using:


LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("A2:A" & LastRow).AutoFilter Field:=1, Criteria1:= _
"<>87036", Operator:=xlOr, Criteria2:="<>120317"
With ActiveSheet.AutoFilter.Range
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
End With
ActiveSheet.ShowAllData
Selection.AutoFilter



I can make this work for ONE number (ie. Either 87036 OR 120317) but I can't make it work where any rows with BOTH of these numbers are kept. See below code which I can use for ONE number:



LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("A2:A" & LastRow).AutoFilter Field:=1, Criteria1:="<>87036"
With ActiveSheet.AutoFilter.Range
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
End With
ActiveSheet.ShowAllData
Selection.AutoFilter


Any help is much appreciated!

mdmackillop
06-17-2016, 07:40 AM
Option Explicit
Sub Test()
Dim r As Range
Set r = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
With r
.AutoFilter Field:=1, Criteria1:="<>87036", Operator:=xlAnd, Criteria2:="<>120317"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub