# Any method to improve the looping speed of 50000 more rows?

• ### Question

• Sub CopyRowsifDuplicate()
Dim a As Range, b As Range, Newloans As Integer
Dim c As Range, d As Range, Matloans As Integer
Dim e As Range, f As Range, Existloans As Integer

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Move New loans
For Each a In Sheets("Output").Range("A1:A50000")
For Each b In Sheets("Today").Range("A1:A50000")
Newloans = Sheets("New loans").Range("A" & Rows.Count).End(xlUp).Row + 1
If a = b Then Sheets("New loans").Range("A" & Newloans).EntireRow.Value = Sheets("Today").Range("A" & b.Row).EntireRow.Value
Next b
Next a

'Move Mature loans
For Each c In Sheets("Output").Range("B1:B50000")
For Each d In Sheets("Previous").Range("A1:A50000")
Matloans = Sheets("Mature loans").Range("A" & Rows.Count).End(xlUp).Row + 1
If c = d Then Sheets("Mature loans").Range("A" & Matloans).EntireRow.Value = Sheets("Previous").Range("A" & d.Row).EntireRow.Value
Next d
Next c

'Move Existing loans
For Each e In Sheets("Output").Range("C1:C50000")
For Each f In Sheets("Previous").Range("A1:A50000")
Existloans = Sheets("Existing loans").Range("A" & Rows.Count).End(xlUp).Row + 1
If e = f Then Sheets("Existing loans").Range("A" & Existloans).EntireRow.Value = Sheets("Previous").Range("A" & f.Row).EntireRow.Value
Next f
Next e

End Sub

• Edited by Sunday, October 21, 2012 2:43 PM
Sunday, October 21, 2012 2:41 PM

### All replies

• That code's complex and appears to be doing 50,000 * 50,000 iterations 3 times. You can't speed that code up. So, what are you trying to do? Please provide a 5 lines of data example as I'm sure a different solution will be significantly quicker. I suspect a combination of vlookup and VBA will be the thing.

Rod Gill

Rod Gill Project Management

Sunday, October 21, 2012 10:19 PM
• On Sun, 21 Oct 2012 14:41:29 +0000, VBAlearner wrote:

You can speed things up considerably by using VBA arrays, and only working on the rows with data.
It is also faster to loop through an array using a syntax of
for i = 1 to n

rather than
for each v in y

Then construct an array with your results and write it back to the worksheet as a last step.

For example, to read a range into an array:

dim v1 as variant
v1 = range("a1",cells(rows.count,"A").end(xlup))

will result in a 2 dimensional array where Dimension 1 = rows and dimension 2 = columns.

When your done, let us say you have constructed an array that is 20000 rows and two columns:

range("c1", cells(20000,"D")) = v2

is all you need to do to write the results back to the sheet.  It is important that you set the destination range to the same size and dimensions as the array you are writing back.

This method is generally orders of magnitude faster, and becomes useful in dealing with large datasets.

Ron
Monday, October 22, 2012 12:14 AM
• I work with .SpecialCells(xlCellTypeConstants)

Maybe this will help to﻿﻿﻿﻿﻿

Tuesday, October 23, 2012 8:38 AM