Deleting rows with Advance Filter in recorded Macro

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.

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



--
Regards,
Tom Ogilvy



"JHL" <JHL@discussions.microsoft.com> wrote in message
news:695CB1C0-0F2D-4632-AAC0-5A156E7EC514@microsoft.com...
> 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.
>

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

Thank You this worked perfectly!

"Tom Ogilvy" wrote:

>
> Rows(Activecell.Row + 1 & ":" & Rows.count).EntireRow.Select
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "JHL" <JHL@discussions.microsoft.com> wrote in message
> news:695CB1C0-0F2D-4632-AAC0-5A156E7EC514@microsoft.com...
> > 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.
> >
>
>
>

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

"abmorgan@gmail.com" wrote:

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