another way to speed the macro .

[jack777531]

hi there

i am using range() function in my macro with loop for next .

part one of this macro the one take most almost 90% of time in this macro .
Calculation in part 1 will happened after copying and pasting the new value as input .
i have few questions regardsing this .

1-How to improve this macro making it run faster ( i need to keep the calculation part on )?

2-can i turn off calculation before copying pasting then turn on the calculation, then off when i am out of next loop ?

3- which different function can be used in place of range() but still can be looped using for next, example ?

4- origin macro loop next was 148000 ,but due to taking long time i reduce the number for testing .

Sub Macro1()

'part 1
    Dim StartTime As Double
    Dim MinutesElapsed As String
    Dim i As Long
    Dim t As Long
    Dim a As Long

    'Remember time when macro starts
    StartTime = Timer
  
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting

    For i = 159 To 2590
    'copy row as input
    Sheets("MAIN").Select
    Range(Cells(i, 2), Cells(i, 12)).Select
    Selection.Copy
    'select where to paste input
    Range("D18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    'paste here in colume for input
    Range("L124").Select
    'Apllication.CutCopyMode = False
    'After calculation done copy the output
    Selection.Copy
    'paste the output here
    Range("U" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Next
'part two
    'if you find true copy and paste
    For t = 159 To 2590
    a = Range("V" & t)
    If a = True Then
    Range(Cells(t, 2), Cells(t, 12)).Select
    
    Selection.Copy
    Range("W112").Select
    ActiveSheet.Paste
    
    Else
    End If
    Next t
    
    'part three
    'select the one in part two and copy it to new loction
    Range("W112:AG112").Select
    Selection.Copy
    
    Range("D18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    
    Application.CutCopyMode = False
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.DisplayPageBreaks = True 'note this is a sheet-level setting

    Application.Calculation = xlCalculationAutomatic
    'Determine how many seconds code took to run
    MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
    'Notify user in seconds
    MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation

End Sub

Thank you

 

[Joe4]

Welcome to the Board!

One thing that should help is to get rid of all the "Select" lines. It is usually not necessary to Select ranges in order to work with them (a lot of this comes from the Macro Recorder which is very literal, and records your every move). So you can often clean-up your code a bit by combining lines where one line ends with ".Select" and the next starts with "Selection.".

See if this runs any faster:

Sub Macro1()

'part 1
    Dim StartTime As Double
    Dim MinutesElapsed As String
    Dim i As Long
    Dim t As Long
    Dim a As Long

    'Remember time when macro starts
    StartTime = Timer
 
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting

    For i = 159 To 2590
    'copy row as input
    Sheets("MAIN").Select
    Range(Cells(i, 2), Cells(i, 12)).Copy
    'select where to paste input
    Range("D18").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    'paste here in colume for input
    Range("L124").Copy
    'paste the output here
    Range("U" & i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
Next
'part two
    'if you find true copy and paste
    For t = 159 To 2590
    a = Range("V" & t)
    If a = True Then
    Range(Cells(t, 2), Cells(t, 12)).Copy Range("W112")
    
    Else
    End If
    Next t
   
    'part three
    'select the one in part two and copy it to new loction
    Range("W112:AG112").Copy
   
    Range("D18").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
   
    Application.CutCopyMode = False
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.DisplayPageBreaks = True 'note this is a sheet-level setting

    Application.Calculation = xlCalculationAutomatic
    'Determine how many seconds code took to run
    MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
    'Notify user in seconds
    MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation

End Sub

Also note that by their very nature, loops are slow and inefficient. So if there are other options to avoid them, it is usually recommended to do so, and just use them as a last resort.
Sometimes you cannot avoid using them, but you can often limit them by dynamically determining just how far you need to go.

For example, say you needed to loop for every value in column A. You can dynamically find the last row with data in column A like this:

Dim lastRow as Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

and can then just loop through those rows like this:

Dim r as Long
For r = 2 to lastRow

 

[Georgiboy]

All of the above and, I would say you would need to replace whatever calculation is happening in 'Range("L124")' with VBA and then make the macro run without the calculation. What calculation is happening after you paste the data into Range("D18")?

 

[Georgiboy]

Without seeing your sheet, your code seems to be doing something like the below:

Sub Macro1()
    Dim StartTime As Double, MinutesElapsed As String
    Dim i As Long, t As Long
    Dim wsMain As Worksheet

    StartTime = Timer
   
    With wsMain
        For i = 159 To 2590
            .Range("D18:N18").Value = .Range(.Cells(i, 2), .Cells(i, 12)).Value
            .Range("U" & i).Value = .Range("L124").Value
            ' *** need to know what is being calculated on the sheet here that makes column V become True ***
            If .Range("V" & i) Then
                .Range("W112:AG112").Value = .Range(.Cells(t, 2), .Cells(t, 12)).Value
                Exit For ' this will exit the for loop when the first True is found in Range("V" & i)
            End If
        Next i
        .Range("D18:N18").Value = .Range("W112:AG112").Value
    End With

    MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
    MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
End Sub

If we can understand what is making '.Range("V" & i)' = True then we could maybe build that into the VBA and remove the need for it to calculate.