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!
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!