You can often see a significant increase in performance by storing all
the rows to delete in a Range type variable and then delete that range
with one Delete operation. For example,
First, declare the variable in which the rows to be delete will be
referenced:
Dim DeleteThese As Range
Then, get rid of
Rows(r).Delete
and replace it with
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(r)
Else
Set DeleteThese = Application.Union(DeleteThese, Rows(r))
End If
Finally, after the Next loop control statement, delete the range
DeleteThese with one single Delete.
DeleteThese.Delete
Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Fri, 12 Dec 2008 02:42:36 -0800 (PST),
wrote:
LastRow = Range("F" & Rows.Count).End(xlUp).Row
For r = 2 To LastRow Step 1 ' Headings in row 1
If Cells(r, "F").Value = "Closed" Then
Rows(r).Copy Destination:=Sheets _
("Interim").Range("A2").Offset(off, 0)
Rows(r).Delete
off = off + 1
End If
Next
Thanks