How can I make this Loop faster, screen updating is off
 

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

How can I make this Loop faster, screen updating is off
 

Turn Calaculation off as well?

--
__________________________________
HTH

Bob

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

How can I make this Loop faster, screen updating is off
 

Hi
Don't know about faster, but you should use

For r = Lastrow to 2 step -1

as you are deleting rows. This would throw off your loop cycling
forwards.
To speed things up don't use a loop. Filter the range for the word
"closed" then copy the whole filtered range to the new sheet in one
go. Then delete the filtered range. Try using the macro recorder while
doing this to see the code.

regards
Paul

On Dec 12, 10:42*am, 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

How can I make this Loop faster, screen updating is off
 

On 12 Dec, 12:42, wrote:
* * * * Rows(r).Delete

With method ClearContents this
loop will work a little faster :

Rows(r).ClearContents

How can I make this Loop faster, screen updating is off
 

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

How can I make this Loop faster, screen updating is off
 

Another option
Instead of looping through each row

try Autofilter - Filter on "Closed" value

Copy, paste, delete


--
mudraker

If my reply has assisted or failed to assist you I welcome your
Feedback.

www.thecodecage.com
------------------------------------------------------------------------
mudraker's Profile: http://www.thecodecage.com/forumz/member.php?userid=18
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=40121