Deleting rows with Advance Filter in recorded Macro
 

Im using Excel 97. Im not a programmer. Using the macro recorder to learn
coding. Im working on a situation where Im 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 dont anticipate
the selection ever being larger than 65000 rows. However I cant 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.

Deleting rows with Advance Filter in recorded Macro
 

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



--
Regards,
Tom Ogilvy



"JHL" wrote in message
...
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.

Deleting rows with Advance Filter in recorded Macro
 

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

Deleting rows with Advance Filter in recorded Macro
 

Thank You this worked perfectly!

"Tom Ogilvy" wrote:


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



--
Regards,
Tom Ogilvy



"JHL" wrote in message
...
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.

Deleting rows with Advance Filter in recorded Macro
 

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

" 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