Deleting rows with Advance Filter in recorded Macro

[Guest]

I’m using Excel 97. I’m not a programmer. Using the macro recorder to learn
coding. I’m working on a situation where I’m selecting data using the
AutoFilter. Once the criteria is made, I want to delete the rows selected.
The recorder lists it as:

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="=DCNSU", Operator:=xlOr, _
Criteria2:="=DUPCTRL"
ActiveCell.Offset(1, 0).Rows("1:65000").EntireRow.Select
Selection.Delete Shift:=xlUp


I changed the rows selection to read “1:65000â€â€¦ because I don’t anticipate
the selection ever being larger than 65000 rows. However I can’t be exactly
sure, and I would perfer it to reflect variable changing rows? Can someone
tell me how to re-code this?

Thanks in advance.

 

[Tom Ogilvy]

Rows(Activecell.Row + 1 & ":" & Rows.count).EntireRow.Select

 

[abmorgan]

I would try something like this:
Looping through values in Column A and deleting the rows for cells
containg your criteria.
This would be quite slow for 65,000 lines though.
There may be a better way.
And I'm not sure if there are any flaws in my code.



Sub DeleteEntries()

Range("A1").Select
Do Until IsEmpty(ActiveCell)
If ActiveCell.Value = "DCNSU" Or ActiveCell.Value = "DUPCTRL" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub


Somethinglikeant

 

[Guest]

Thank You this worked perfectly!

Rows(Activecell.Row + 1 & ":" & Rows.count).EntireRow.Select

 

[Guest]

Thank you, I had a loop performing this, but thought the autofilter would be
faster, and it is.