I have a spreadsheet with 8,000 rows that I have performed some work on. In
column W "Research" and numbers appear. I have written in my macro so that
the data is sorted so that all the Research appears in the first rows.

How do I delete rows, as quickly as possible, that have Research in Column W?

I wrote the following Loop but it doesn't work and I am not sure that a Loop
is the quickest way to accomplish this. Column V is blank. I was trying to
establish a block of rows to be deleted and delete all at once.

Sub Macro1()
Range("V7").Select
ActiveCell.Formula = "1"
Range("W7").Select
X = ActiveCell.Row

Do While Cells(X, 23).Text = "Research"
X = X + 1
Loop

ActiveCell.Select
ActiveCell.Offset(-1, -1).Select
Selection.End(xlUp).Select
Selection.EntireRow.Delete Shift:=xlUp


End Sub

I have found that Autofilter is the fastest way to delete occurences of any
kind, please modify to suit your needs.

Range("W:W").AutoFilter Field:=1, Criteria1:="=Research", Operator:=xlAnd
Columns("W:W").SpecialCells(xlCellTypeVisible).EntireRow.Delete
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"iashorty" wrote:

> I have a spreadsheet with 8,000 rows that I have performed some work on. In
> column W "Research" and numbers appear. I have written in my macro so that
> the data is sorted so that all the Research appears in the first rows.
>
> How do I delete rows, as quickly as possible, that have Research in Column W?
>
> I wrote the following Loop but it doesn't work and I am not sure that a Loop
> is the quickest way to accomplish this. Column V is blank. I was trying to
> establish a block of rows to be deleted and delete all at once.
>
> Sub Macro1()
> Range("V7").Select
> ActiveCell.Formula = "1"
> Range("W7").Select
> X = ActiveCell.Row
>
> Do While Cells(X, 23).Text = "Research"
> X = X + 1
> Loop
>
> ActiveCell.Select
> ActiveCell.Offset(-1, -1).Select
> Selection.End(xlUp).Select
> Selection.EntireRow.Delete Shift:=xlUp
>
>
> End Sub
>

Fantastic, thank you.

"Michael" wrote:

> I have found that Autofilter is the fastest way to delete occurences of any
> kind, please modify to suit your needs.
>
> Range("W:W").AutoFilter Field:=1, Criteria1:="=Research", Operator:=xlAnd
> Columns("W:W").SpecialCells(xlCellTypeVisible).EntireRow.Delete
> --
> If this posting was helpful, please click on the Yes button.
> Regards,
>
> Michael Arch.
>
>
>
>
> "iashorty" wrote:
>
> > I have a spreadsheet with 8,000 rows that I have performed some work on. In
> > column W "Research" and numbers appear. I have written in my macro so that
> > the data is sorted so that all the Research appears in the first rows.
> >
> > How do I delete rows, as quickly as possible, that have Research in Column W?
> >
> > I wrote the following Loop but it doesn't work and I am not sure that a Loop
> > is the quickest way to accomplish this. Column V is blank. I was trying to
> > establish a block of rows to be deleted and delete all at once.
> >
> > Sub Macro1()
> > Range("V7").Select
> > ActiveCell.Formula = "1"
> > Range("W7").Select
> > X = ActiveCell.Row
> >
> > Do While Cells(X, 23).Text = "Research"
> > X = X + 1
> > Loop
> >
> > ActiveCell.Select
> > ActiveCell.Offset(-1, -1).Select
> > Selection.End(xlUp).Select
> > Selection.EntireRow.Delete Shift:=xlUp
> >
> >
> > End Sub
> >