Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a faster way to loop thru cells | Excel Programming | |||
For Next loop faster with counter after Next? | Excel Programming | |||
Faster way to loop through two ranges | Excel Programming | |||
Is there a faster loop than this | Excel Programming | |||
Faster For-Next Loop? | Excel Programming |