#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    2
    Rep Power
    0

    Macro to copy formulas down 73,388 rows


    I have a macro that will copy formulas from cells B2, C2, D2 & E2 down a certain number of rows (currently 73,388 rows!). The number of rows is in cell G1. The code I have so far is WAY too slow! It is taking 1 minute just to do 4,000 rows (I manually set G1 to 4,000). By comparison when I do a manual copy & paste of the formulas, it takes 10 seconds for the 4,000 items and 3 minutes for the 73,388 items. B & C have Vlookups; D&E have simple if statements. Turning calc on & off in the macro appears to have absolutely no effect. I have tried putting the four copies into the same For loop and it is still way too slow. Does anyone have any suggestions?

    PHP Code:
    Sub Step4CopyDownVlookup()
    '
    Step4CopyDownVlookup Macro
    '
    '
    ' Give StatusBar Message
        Application.StatusBar = "Please wait - Copying Vlookup Calculations..."
        Application.Wait Now + TimeValue("00:00:02")
        
        Dim i As Long
        
        Range("b2").Select
        Application.Calculation = xlManual
         
        For i = 2 To Range("g1").Value - 1
            Cells(i + 1, 2) = Cells(i, 2).FormulaR1C1
            Range("I1") = i
        Next i
        For i = 2 To Range("g1").Value - 1
            Cells(i + 1, 3) = Cells(i, 3).FormulaR1C1
            Range("I1") = i
        Next i

        For i = 2 To Range("g1").Value - 1
            Cells(i + 1, 4) = Cells(i, 4).FormulaR1C1
            Range("I1") = i
        Next i

        For i = 2 To Range("g1").Value - 1
            Cells(i + 1, 5) = Cells(i, 5).FormulaR1C1
            Range("I1") = i
        Next i
        Application.Calculation = xlAutomatic
    End Sub 
  2. #2
  3. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Takes a second or two:

    Code:
        
        With ActiveSheet
            .Range("B2:E2").Copy
            .Range("B3:E" & .Range("g1")).PasteSpecial (xlPasteFormulas)
        End With
    Last edited by medialint; September 15th, 2011 at 01:47 PM.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    2
    Rep Power
    0
    Oh my gosh!
    Thank you so much! This is exactly what I needed. With 73,000+ items, it does take more than a few seconds - 3 minutes actually. I am greatly appreciative.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    23
    Rep Power
    0

    nice reply


    Originally Posted by medialint
    Takes a second or two:

    Code:
        
        With ActiveSheet
            .Range("B2:E2").Copy
            .Range("B3:E" & .Range("g1")).PasteSpecial (xlPasteFormulas)
        End With
    to the point!

IMN logo majestic logo threadwatch logo seochat tools logo