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

  • 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 VBAlearner 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

    The one and only Project VBA Book

    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.

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

    Maybe this will help to

    Tuesday, October 23, 2012 8:38 AM