Deleting rows with Advance Filter in recorded Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JHL - 24 Jan 2006 18:21 GMT

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.

Reply to this Message

Tom Ogilvy - 24 Jan 2006 18:49 GMT

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

Signature

Regards,
Tom Ogilvy

> 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.
[quoted text clipped - 8 lines]
>
> 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.

Reply to this Message

JHL - 24 Jan 2006 19:27 GMT

Thank You this worked perfectly!

> Rows(Activecell.Row + 1 & ":" & Rows.count).EntireRow.Select
>
[quoted text clipped - 21 lines]
> >
> > Thanks in advance.

Reply to this Message

abmorgan@gmail.com - 24 Jan 2006 18:51 GMT

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

Reply to this Message

JHL - 24 Jan 2006 19:31 GMT

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

> I would try something like this:
> Looping through values in Column A and deleting the rows for cells
[quoted text clipped - 17 lines]
>
> Somethinglikeant

Reply to this Message