Fastest way to "Replace" "auto fill" and then loop in Excel VBA I am wondering if there's any way to improve the performance of this simple "replace" / "autofill" loops that I created in excel? 

Sub Macro1()



Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False



For i = 1 To 50
Do


Sheets("Sheet1").Select



'Range("Q1:Q6").Value are random generated number using randbetween (=RANDBETWEEN(3,103))
'save the random created value to Range("U1:U6")
Sheet1.Range("U1:U6").Value = Sheet1.Range("Q1:Q6").Value




Range("AA1").Select




    'Column u1 to u6 are random values generated using =RANDBETWEEN(3,103).
'example of  Range("aa1") Pre-entered formula: =a5+b95+c48+d29+e62+f29
'replace the "a5" in range("aa1") with "a?", with the "?" being the random number in U1.
Selection.Replace What:="a" &amp; Range("t1") &amp; "+", Replacement:="a" &amp; Range("u1") &amp; "+", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    'replace the "B95" in range("aa1") with "a?", with the "?" being the random number in U2.
Selection.Replace What:="b" &amp; Range("t2") &amp; "+", Replacement:="b" &amp; Range("u2") &amp; "+", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    'replace the "C48" in range("aa1") with "a?", with the "?" being the random number in U3.
Selection.Replace What:="c" &amp; Range("t3") &amp; "+", Replacement:="c" &amp; Range("u3") &amp; "+", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    'replace the "D29" in range("aa1") with "a?", with the "?" being the random number in U4.
Selection.Replace What:="d" &amp; Range("t4") &amp; "+", Replacement:="d" &amp; Range("u4") &amp; "+", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    'replace the "E62" in range("aa1") with "a?", with the "?" being the random number in U5.

Selection.Replace What:="e" &amp; Range("t5") &amp; "+", Replacement:="e" &amp; Range("u5") &amp; "+", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

    'replace the "F29" in range("aa1") with "a?", with the "?" being the random number in U6.
Selection.Replace What:="f" &amp; Range("t6") &amp; ",", Replacement:="f" &amp; Range("u6") &amp; ",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False



'Autofill the modified formula in aa1 to aa1301.
Range("AA1").AutoFill Destination:=Range("AA1:AA1301")
'Manual re-calulate the autofills.
'Manual re-generate the random number

Calculate

    "Copy the number from U1:U6 (the =randbetween(3,103) to T1:T6 to save the random generated number.
'    
Sheet1.Range("T1:T6").Value = Sheet1.Range("U1:U6").Value



                         Loop Until Range("ac3") = 1 And Range("h8") &gt;= 8 And Range("o3") = 0 And Range("o4") &lt;&gt; 0




Next i


 Application.Calculation = xlCalculationAutomatic
 Application.ScreenUpdating = True
 Application.EnableEvents = True
 Application.DisplayStatusBar = True



Beep

End Sub


Any better way to speed up the "replace" and the "autofill" or the whole macro even? It's pretty simple, just now fast enough!

Thanks
Just indenting the code and removing the excessive whitespace you have yields something significantly more readable (after also fixing the compile-error with the non-comment after Calculate):


Sub Macro1()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayStatusBar = False

    For i = 1 To 50
        Do
            Sheets("Sheet1").Select

            'Range("Q1:Q6").Value are random generated number using randbetween (=RANDBETWEEN(3,103))
            'save the random created value to Range("U1:U6")
            Sheet1.Range("U1:U6").Value = Sheet1.Range("Q1:Q6").Value

            Range("AA1").Select

            'Column u1 to u6 are random values generated using =RANDBETWEEN(3,103).
            'example of  Range("aa1") Pre-entered formula: =a5+b95+c48+d29+e62+f29
            'replace the "a5" in range("aa1") with "a?", with the "?" being the random number in U1.
            Selection.Replace What:="a" &amp; Range("t1") &amp; "+", Replacement:="a" &amp; Range("u1") &amp; "+", LookAt:=xlPart, _
                              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                              ReplaceFormat:=False

            'replace the "B95" in range("aa1") with "a?", with the "?" being the random number in U2.
            Selection.Replace What:="b" &amp; Range("t2") &amp; "+", Replacement:="b" &amp; Range("u2") &amp; "+", LookAt:=xlPart, _
                              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                              ReplaceFormat:=False

            'replace the "C48" in range("aa1") with "a?", with the "?" being the random number in U3.
            Selection.Replace What:="c" &amp; Range("t3") &amp; "+", Replacement:="c" &amp; Range("u3") &amp; "+", LookAt:=xlPart, _
                              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                              ReplaceFormat:=False

            'replace the "D29" in range("aa1") with "a?", with the "?" being the random number in U4.
            Selection.Replace What:="d" &amp; Range("t4") &amp; "+", Replacement:="d" &amp; Range("u4") &amp; "+", LookAt:=xlPart, _
                              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                              ReplaceFormat:=False

            'replace the "E62" in range("aa1") with "a?", with the "?" being the random number in U5.
            Selection.Replace What:="e" &amp; Range("t5") &amp; "+", Replacement:="e" &amp; Range("u5") &amp; "+", LookAt:=xlPart, _
                              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                              ReplaceFormat:=False

            'replace the "F29" in range("aa1") with "a?", with the "?" being the random number in U6.
            Selection.Replace What:="f" &amp; Range("t6") &amp; ",", Replacement:="f" &amp; Range("u6") &amp; ",", LookAt:=xlPart, _
                              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                              ReplaceFormat:=False

            'Autofill the modified formula in aa1 to aa1301.
            Range("AA1").AutoFill Destination:=Range("AA1:AA1301")
            'Manual re-calulate the autofills.
            'Manual re-generate the random number

            Calculate

            'Copy the number from U1:U6 (the =randbetween(3,103) to T1:T6 to save the random generated number.
            Sheet1.Range("T1:T6").Value = Sheet1.Range("U1:U6").Value

        Loop Until Range("ac3") = 1 And Range("h8") &gt;= 8 And Range("o3") = 0 And Range("o4") &lt;&gt; 0
    Next i

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True

    Beep
End Sub



Let's start with this.

The next step is breaking this into more manageable chunks:

Private Sub EnablePerformanceOptimizations()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayStatusBar = False
End Sub


Putting this together with the other block at the end of your current Macro1 we get:

Private Sub PerformanceOptimizations(ByVal enable As Boolean)
    Application.Calculation = IIf(enable, xlCalculationManual, xlCalculationAutomatic)
    Application.ScreenUpdating = Not enable
    Application.EnableEvents = Not enable
    Application.DisplayStatusBar = Not enable
End Sub


Now that this is finished we can "reimagine" Macro1 as the following 

Public Sub Macro1()
    PerformanceOptimizations(True)

    GenerateAndCopyRandomNumbers()

    PerformanceOptimizations(False)
End Sub


Suddenly we can see "exactly" what Macro1 does. It does performance optimizations before generating random numbers and then disables the optimizations again.

The next step is to optimize that number generation.

The code you posted here does never use the loop variable of the For Next loop. We can just completely drop that loop, which already should reduce the runtime to \$\frac{1}{50}\$ of the current runtime...

The next step is optimizing these Replace calls.

The first step towards that would usually be to get rid of Selection because that's slow. I'll ignore that for now and just reformat the code (again) just to see what's actually going on:

Private Sub GenerateAndSaveRandomNumbers()
    Do
        Sheets("Sheet1").Select

        'Range("Q1:Q6").Value are random generated number using randbetween (=RANDBETWEEN(3,103))
        'save the random created value to Range("U1:U6")
        Sheet1.Range("U1:U6").Value = Sheet1.Range("Q1:Q6").Value

        Range("AA1").Select

        'Column u1 to u6 are random values generated using =RANDBETWEEN(3,103).
        'example of  Range("aa1") Pre-entered formula: =a5+b95+c48+d29+e62+f29
        'replace the "a5" in range("aa1") with "a?", with the "?" being the random number in U1.
        Selection.Replace What:="a" &amp; Range("t1") &amp; "+", _
                          Replacement:="a" &amp; Range("u1") &amp; "+", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, _
                          SearchFormat:=False, ReplaceFormat:=False

        'replace the "B95" in range("aa1") with "a?", with the "?" being the random number in U2.
        Selection.Replace What:="b" &amp; Range("t2") &amp; "+", _
                          Replacement:="b" &amp; Range("u2") &amp; "+", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, _
                          SearchFormat:=False, ReplaceFormat:=False

        'replace the "C48" in range("aa1") with "a?", with the "?" being the random number in U3.
        Selection.Replace What:="c" &amp; Range("t3") &amp; "+", _
                          Replacement:="c" &amp; Range("u3") &amp; "+", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, _
                          SearchFormat:=False, ReplaceFormat:=False

        'replace the "D29" in range("aa1") with "a?", with the "?" being the random number in U4.
        Selection.Replace What:="d" &amp; Range("t4") &amp; "+", _
                          Replacement:="d" &amp; Range("u4") &amp; "+", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, _
                          SearchFormat:=False, ReplaceFormat:=False

        'replace the "E62" in range("aa1") with "a?", with the "?" being the random number in U5.
        Selection.Replace What:="e" &amp; Range("t5") &amp; "+", _
                          Replacement:="e" &amp; Range("u5") &amp; "+", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, _ 
                          SearchFormat:=False, ReplaceFormat:=False

        'replace the "F29" in range("aa1") with "a?", with the "?" being the random number in U6.
        Selection.Replace What:="f" &amp; Range("t6") &amp; ",", _
                          Replacement:="f" &amp; Range("u6") &amp; ",", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, _
                          SearchFormat:=False, ReplaceFormat:=False

        'Autofill the modified formula in aa1 to aa1301.
        Range("AA1").AutoFill Destination:=Range("AA1:AA1301")
        'Manual re-calulate the autofills.
        'Manual re-generate the random number

        Calculate

        'Copy the number from U1:U6 (the =randbetween(3,103) to T1:T6 to save the random generated number.
        Sheet1.Range("T1:T6").Value = Sheet1.Range("U1:U6").Value

    Loop Until Range("ac3") = 1 And Range("h8") &gt;= 8 And Range("o3") = 0 And Range("o4") &lt;&gt; 0
End Sub()


Now since Replace has quite a bunch of optional arguments, and you're overwriting most of them with defaults or unhelpful values, let's drop them:


MatchCase defaults to False
MatchByte isn't used in your code
SearchFormat would boil down to a Falsey if you didn't pass it
ReplaceFormat same considerations as for SearchFormat apply.


Interestingly we can even go so far as to abuse the fact that LookAt, SearchOrder and MatchCase (as well as MatchByte) are "saved" between invocations to collapse all this into the following replacement calls:

        'Column u1 to u6 are random values generated using =RANDBETWEEN(3,103).
        'example of  Range("aa1") Pre-entered formula: =a5+b95+c48+d29+e62+f29
        'replace the "a5" in range("aa1") with "a?", with the "?" being the random number in U1.
        Selection.Replace What:="a" &amp; Range("t1") &amp; "+", _
                          Replacement:="a" &amp; Range("u1") &amp; "+", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False

        'replace the "B95" in range("aa1") with "a?", with the "?" being the random number in U2.
        Selection.Replace What:="b" &amp; Range("t2") &amp; "+", _
                          Replacement:="b" &amp; Range("u2") &amp; "+"

        'replace the "C48" in range("aa1") with "a?", with the "?" being the random number in U3.
        Selection.Replace What:="c" &amp; Range("t3") &amp; "+", _
                          Replacement:="c" &amp; Range("u3") &amp; "+"

        'replace the "D29" in range("aa1") with "a?", with the "?" being the random number in U4.
        Selection.Replace What:="d" &amp; Range("t4") &amp; "+", _
                          Replacement:="d" &amp; Range("u4") &amp; "+"

        'replace the "E62" in range("aa1") with "a?", with the "?" being the random number in U5.
        Selection.Replace What:="e" &amp; Range("t5") &amp; "+", _
                          Replacement:="e" &amp; Range("u5") &amp; "+"

        'replace the "F29" in range("aa1") with "a?", with the "?" being the random number in U6.
        Selection.Replace What:="f" &amp; Range("t6") &amp; ",", _
                          Replacement:="f" &amp; Range("u6") &amp; ","


Continued simplfications include not using Selection and using a With block:

With Sheet1.Range("AA1")
    .Replace What:="a" &amp; Range("t1") &amp; "+", _
             Replacement:="a" &amp; Range("u1") &amp; "+", LookAt:=xlPart, _
             SearchOrder:=xlByRows, MatchCase:=False
    'replace the "B95" in range("aa1") with "a?", with the "?" being the random number in U2.
    .Replace What:="b" &amp; Range("t2") &amp; "+", Replacement:="b" &amp; Range("u2") &amp; "+"

    'replace the "C48" in range("aa1") with "a?", with the "?" being the random number in U3.
    .Replace What:="c" &amp; Range("t3") &amp; "+", Replacement:="c" &amp; Range("u3") &amp; "+"

    'replace the "D29" in range("aa1") with "a?", with the "?" being the random number in U4.
    .Replace What:="d" &amp; Range("t4") &amp; "+", Replacement:="d" &amp; Range("u4") &amp; "+"

    'replace the "E62" in range("aa1") with "a?", with the "?" being the random number in U5.
    .Replace What:="e" &amp; Range("t5") &amp; "+", Replacement:="e" &amp; Range("u5") &amp; "+"

    'replace the "F29" in range("aa1") with "a?", with the "?" being the random number in U6.
    .Replace What:="f" &amp; Range("t6") &amp; ",", Replacement:="f" &amp; Range("u6") &amp; ","


This should give you another performance boost and will continue to work, even if the user changes the Selection during the execution of the macro.