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" & Range("t1") & "+", Replacement:="a" & Range("u1") & "+", 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" & Range("t2") & "+", Replacement:="b" & Range("u2") & "+", 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" & Range("t3") & "+", Replacement:="c" & Range("u3") & "+", 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" & Range("t4") & "+", Replacement:="d" & Range("u4") & "+", 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" & Range("t5") & "+", Replacement:="e" & Range("u5") & "+", 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" & Range("t6") & ",", Replacement:="f" & Range("u6") & ",", 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") >= 8 And Range("o3") = 0 And Range("o4") <> 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" & Range("t1") & "+", _
Replacement:="a" & Range("u1") & "+", 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" & Range("t2") & "+", _
Replacement:="b" & Range("u2") & "+", 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" & Range("t3") & "+", _
Replacement:="c" & Range("u3") & "+", 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" & Range("t4") & "+", _
Replacement:="d" & Range("u4") & "+", 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" & Range("t5") & "+", _
Replacement:="e" & Range("u5") & "+", 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" & Range("t6") & ",", _
Replacement:="f" & Range("u6") & ",", 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") >= 8 And Range("o3") = 0 And Range("o4") <> 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" & Range("t1") & "+", _
Replacement:="a" & Range("u1") & "+", 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" & Range("t2") & "+", _
Replacement:="b" & Range("u2") & "+"
'replace the "C48" in range("aa1") with "a?", with the "?" being the random number in U3.
Selection.Replace What:="c" & Range("t3") & "+", _
Replacement:="c" & Range("u3") & "+"
'replace the "D29" in range("aa1") with "a?", with the "?" being the random number in U4.
Selection.Replace What:="d" & Range("t4") & "+", _
Replacement:="d" & Range("u4") & "+"
'replace the "E62" in range("aa1") with "a?", with the "?" being the random number in U5.
Selection.Replace What:="e" & Range("t5") & "+", _
Replacement:="e" & Range("u5") & "+"
'replace the "F29" in range("aa1") with "a?", with the "?" being the random number in U6.
Selection.Replace What:="f" & Range("t6") & ",", _
Replacement:="f" & Range("u6") & ","
Continued simplfications include not using Selection
and using a With
block:
With Sheet1.Range("AA1")
.Replace What:="a" & Range("t1") & "+", _
Replacement:="a" & Range("u1") & "+", 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" & Range("t2") & "+", Replacement:="b" & Range("u2") & "+"
'replace the "C48" in range("aa1") with "a?", with the "?" being the random number in U3.
.Replace What:="c" & Range("t3") & "+", Replacement:="c" & Range("u3") & "+"
'replace the "D29" in range("aa1") with "a?", with the "?" being the random number in U4.
.Replace What:="d" & Range("t4") & "+", Replacement:="d" & Range("u4") & "+"
'replace the "E62" in range("aa1") with "a?", with the "?" being the random number in U5.
.Replace What:="e" & Range("t5") & "+", Replacement:="e" & Range("u5") & "+"
'replace the "F29" in range("aa1") with "a?", with the "?" being the random number in U6.
.Replace What:="f" & Range("t6") & ",", Replacement:="f" & Range("u6") & ","
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.