Deleting rows when cell text reads "Research"
 

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

Deleting rows when cell text reads "Research"
 

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).Ent ireRow.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

Deleting rows when cell text reads "Research"
 

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).Ent ireRow.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