I am a self taught VBA user. I was asked to look at the code from a tool which has stopped working due to a data overflow and "fix" it. I was told the code was 'optimised' so that it took only 2 hours to run instead of four. They did this by remove loops.

I didn't have an issue with my 64-bit Excel, but another user stepped through it for me and the below lines prompted the 'out of memory' error.

Calculations are set to manual and the screen is frozen at the beginning of the code.

In my limited experience I've had faster results doing a loop and avoiding setting formulas in the document. There are around 500,000 lines of data currently.

Would I be better off changing it to a loop? Or would that add hours onto the computation time? I'm happy to post the full code if anyone wants to see it, but it is not annotated and none of the variables are defined so it is a bit of a mess. I'm cleaning it up as I decipher the code.

Sheets("Main Tab").Range("O2:O" & LastRow).Formula = "=IF(AND(N2>IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""), N2<>""""),IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""),N2)"Sheets("Main Tab").Range("I2:O" & LastRow).Value = Sheets("Main Tab").Range("I2:O" & LastRow).Value

Full code (Calculate_Click is what is causing the issue):

 Sub Clear_Click()    DisableOptimize    UnfilterAll    Application.Calculation = xlCalculationManual    Application.EnableEvents = False    MainTabLastColum = "AU"    Sheets("Order Upload").AutoFilterMode = False    Sheets("Main Tab").AutoFilterMode = False    Sheets("Microstrategy Data").AutoFilterMode = False    Sheets("Velocity").AutoFilterMode = False    LastRow = Sheets("Order Upload").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row        If LastRow > 1 Then            Sheets("Order Upload").Range("A2:K" & LastRow).ClearContents        End If    LastRow = Sheets("SKU-DC Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row        If LastRow > 1 Then            Sheets("SKU-DC Summary").Range("A2:S" & LastRow).ClearContents        End If    LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row        If LastRow > 1 Then            Sheets("Main Tab").Range("A2:" & MainTabLastColum & LastRow).ClearContents        End If    LastRow = Sheets("Microstrategy Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row        If LastRow > 1 Then            Sheets("Microstrategy Data").Range("A2:H" & LastRow).ClearContents        End If    LastRow = Sheets("Velocity").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row        If LastRow > 1 Then            Sheets("Velocity").Range("J2:N" & LastRow).ClearContents            Sheets("Velocity").Range("V2:V" & LastRow).ClearContents        End If    MainTabLastColum = Null    QuantityLastColumn = Null    LastRow = NullEnd SubSub LoadMicroData2()    Dim StartTime As Double    Dim SecondsElapsed As Double    StartTime = Timer    DisableOptimize    LastRowMsCopyTo = Sheets("Microstrategy Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    LastRowMsCopyFrom = Sheets("Data Input Microstrategy").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Call CopyValues(Sheets("Data Input Microstrategy").Range("A7:A" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("A2:A" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Data Input Microstrategy").Range("B7:B" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("B2:B" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Data Input Microstrategy").Range("C7:C" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("C2:C" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Data Input Microstrategy").Range("D7:D" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("E2:E" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Data Input Microstrategy").Range("E7:E" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("F2:F" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Data Input Microstrategy").Range("F7:F" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("G2:G" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Data Input Microstrategy").Range("G7:G" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("H2:H" & LastRowMsCopyFrom))    LastRowMsCopyTo = Sheets("Microstrategy Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyTo).Formula = "=Index('Data Input by Account'!$D:$D,match($A2,'Data Input by Account'!$A:$A,false))"    Worksheets("Microstrategy Data").UsedRange.Columns("D").Calculate    Call CopyValues(Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Microstrategy Data").Range("A2:A" & LastRowMsCopyFrom), Sheets("Main Tab").Range("A2:A" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Microstrategy Data").Range("B2:B" & LastRowMsCopyFrom), Sheets("Main Tab").Range("B2:B" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Microstrategy Data").Range("C2:C" & LastRowMsCopyFrom), Sheets("Main Tab").Range("C2:C" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyFrom), Sheets("Main Tab").Range("D2:D" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Microstrategy Data").Range("E2:E" & LastRowMsCopyFrom), Sheets("Main Tab").Range("E2:E" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Microstrategy Data").Range("F2:F" & LastRowMsCopyFrom), Sheets("Main Tab").Range("F2:F" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Microstrategy Data").Range("G2:G" & LastRowMsCopyFrom), Sheets("Main Tab").Range("G2:G" & LastRowMsCopyFrom))    Call CopyValues(Sheets("Microstrategy Data").Range("H2:H" & LastRowMsCopyFrom), Sheets("Main Tab").Range("H2:H" & LastRowMsCopyFrom))    LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Application.CutCopyMode = False    LastRowMsCopyTo = Null    LastRowMsCopyFrom = Null    LastRow = NullEnd SubSub CopyValues(rngSource As Range, rngTarget As Range)    rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.ValueEnd SubSub Calculate_Click()    MainTabLastColum = "AU"    QuantityLastColumn = "O"    Worksheets("Main Tab").Select    DisableOptimize    LastRowAvail = Sheets("Quantity Available").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Sheets("Quantity Available").Range("E2:E" & LastRowAvail).Formula = "=CONCATENATE(A2,B2)"    Sheets("Quantity Available").Range("F2:F" & LastRowAvail).Formula = "=CONCATENATE(A2,Upper(B2),C2)"    Sheets("Quantity Available").Range("E2:F" & LastRowAvail).Value = Sheets("Quantity Available").Range("E2:F" & LastRowAvail).Value    ActiveWorkbook.Worksheets("Quantity Available").Sort.SortFields.Clear    ActiveWorkbook.Worksheets("Quantity Available").Sort.SortFields.Add Key:=Range( _        "C2:C" & LastRowAvail), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _        xlSortNormal    ActiveWorkbook.Worksheets("Quantity Available").Sort.SortFields.Add Key:=Range( _        "E2:E" & LastRowAvail), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _        xlSortNormal    With ActiveWorkbook.Worksheets("Quantity Available").Sort        .SetRange Range("A1:F" & LastRowAvail)        .Header = xlYes        .MatchCase = False        .Orientation = xlTopToBottom        .Apply    End With    LastRowData = Sheets("Data Input by Account").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Sheets("Data Input by Account").Range("C2:C" & LastRowData).Formula = "=VLOOKUP(B7,Table5,2,FALSE)"    Sheets("Data Input by Account").Range("C2:C" & LastRowData).Value = Sheets("Data Input by Account").Range("C2:C" & LastRowData).Value    ActiveWorkbook.Worksheets("Data Input by Account").Sort.SortFields.Clear    ActiveWorkbook.Worksheets("Data Input by Account").Sort.SortFields.Add Key:=Range( _        "A2:A" & LastRowData), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _        xlSortNormal    With ActiveWorkbook.Worksheets("Data Input by Account").Sort        .SetRange Range("A1:D" & LastRowData)        .Header = xlYes        .MatchCase = False        .Orientation = xlTopToBottom        .Apply    End With    VelocityLastRow = Sheets("Velocity").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Sheets("Velocity").Range("J2:J" & VelocityLastRow).Formula = "=CONCATENATE(A2,D2,E2,I2)"    Sheets("Velocity").Range("K2:K" & VelocityLastRow).Formula = "=F2"    Sheets("Velocity").Range("L2:L" & VelocityLastRow).Formula = "=G2"    Sheets("Velocity").Range("M2:M" & VelocityLastRow).Formula = "=H2"    Sheets("Velocity").Range("N2:N" & VelocityLastRow).Formula = "=C2"    Sheets("Velocity").Range("J2:N" & VelocityLastRow).Value = Sheets("Velocity").Range("J2:N" & VelocityLastRow).Value    ActiveWorkbook.Worksheets("Velocity").Sort.SortFields.Clear    ActiveWorkbook.Worksheets("Velocity").Sort.SortFields.Add Key:=Range( _        "J2:J" & VelocityLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _        xlSortNormal    With ActiveWorkbook.Worksheets("Velocity").Sort        .SetRange Range("A1:N" & VelocityLastRow)        .Header = xlYes        .MatchCase = False        .Orientation = xlTopToBottom        .Apply    End With    Sheets("Velocity").Range("V2:V" & VelocityLastRow).Formula = "=CONCATENATE(A2,I2)"    Sheets("Velocity").Range("V2:V" & VelocityLastRow).Value = Sheets("Velocity").Range("V2:V" & VelocityLastRow).Value    LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Sheets("Main Tab").Range("U2:U" & LastRow).Formula = "=CONCATENATE(E2,C2)"    Sheets("Main Tab").Range("U2:U" & LastRow).Value = Sheets("Main Tab").Range("U2:U" & LastRow).Value    Sheets("Main Tab").Range("T2:W" & LastRow).Value = Sheets("Main Tab").Range("T2:W" & LastRow).Value    ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Clear    ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Add Key:=Range( _        "U2:U" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _        xlSortNormal    With ActiveWorkbook.Worksheets("Main Tab").Sort        .SetRange Range("A1:AU" & LastRow)        .Header = xlYes        .MatchCase = False        .Orientation = xlTopToBottom        .Apply    End With    Sheets("Main Tab").Range("I2:I" & LastRow).Formula = "=IF(H2<>0,F2/H2)"    Sheets("Main Tab").Range("J2:J" & LastRow).Formula = "=IF(VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:K$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(U2,D2,CalculateWeek), VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:K$" & VelocityLastRow & ",2,TRUE), ""Missing"")"    Sheets("Main Tab").Range("K2:K" & LastRow).Formula = "=IF(VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(U2,D2,CalculateWeek), VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",5,TRUE), ""Missing"")"    Sheets("Main Tab").Range("L2:L" & LastRow).Formula = "=IF(AND(K2<>0,I2>K2),ROUND((F2/K2)/J2,0.1),"""")"    Sheets("Main Tab").Range("M2:M" & LastRow).Formula = "=IF(AND(F2>0,L2<>""""),L2-H2,"""")"    Sheets("Main Tab").Range("N2:N" & LastRow).Formula = "=IF(AND(L2>0,L2<>"""",M2<>""""),FLOOR(M2/IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",3,TRUE), ""Missing""),1),"""")"    Sheets("Main Tab").Range("O2:O" & LastRow).Formula = "=IF(AND(N2>IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""), N2<>""""),IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""),N2)"    Sheets("Main Tab").Range("I2:O" & LastRow).Value = Sheets("Main Tab").Range("I2:O" & LastRow).Value    Sheets("Main Tab").Range("Z2:Z" & LastRow).Formula = "=ROUND((N2*K2),0)"    Sheets("Main Tab").Range("Z2:Z" & LastRow).Value = Sheets("Main Tab").Range("Z2:Z" & LastRow).Value    ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Clear    ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Add Key:=Range( _        "Z2:Z" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _        xlSortNormal    With ActiveWorkbook.Worksheets("Main Tab").Sort        .SetRange Range("A1:AU" & LastRow)        .Header = xlYes        .MatchCase = False        .Orientation = xlTopToBottom        .Apply    End With    Sheets("Main Tab").Range("X2:X" & LastRow).Formula = "=INDEX('Quantity Available'!$D$2:$D$" & LastRowAvail & ",MATCH(U2&""LIBERTY"",'Quantity Available'!$F$2:$F$" & LastRowAvail & ",FALSE))"    Sheets("Main Tab").Range("X2:X" & LastRow).Value = Sheets("Main Tab").Range("X2:X" & LastRow).Value    Dim i As Long    Dim j As Long    Dim myval1 As Long    Dim myval2 As Long    Dim myval3 As Long    Dim MyRange1 As Range    Dim MyRange2 As Range    Dim MyRange3 As Range    For i = 2 To LastRow        Set MyRange1 = Worksheets("Main Tab").Range("U1:U" & i)        Set MyRange2 = Worksheets("Main Tab").Range("Z1:Z" & i)        Set MyRange3 = Worksheets("Main Tab").Range("X" & i)            myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("U" & i).Value, MyRange2)            myval2 = MyRange3            myval3 = myval2 - myval1        Worksheets("Main Tab").Cells(i, 18).Value = myval3    Next    Sheets("Main Tab").Range("S2:S" & LastRow) = "LIBERTY"    Sheets("Main Tab").Range("AC2:AC" & LastRow).Formula = "=IF(R2<0,""C"","""")"    Sheets("Main Tab").Range("AC2:AC" & LastRow).Value = Sheets("Main Tab").Range("AC2:AC" & LastRow).Value    Sheets("Main Tab").Range("AB2:AB" & LastRow).Formula = "=IF(AND(Z2>VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!$J$2:$N$" & VelocityLastRow & ",4), Z2<>""""),VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!$J$2:$N$" & VelocityLastRow & ",4),Z2)"    Sheets("Main Tab").Range("AB2:AB" & LastRow).Value = Sheets("Main Tab").Range("AB2:AB" & LastRow).Value    Sheets("Main Tab").Range("AA2:AA" & LastRow).Formula = "=IF(AC2=""C"",0,AB2)"    Sheets("Main Tab").Range("AA2:AA" & LastRow).Value = Sheets("Main Tab").Range("AA2:AA" & LastRow).Value    Dim myval4 As Long    Dim MyRange4 As Range    Dim MyRange5 As Range    For j = 2 To 500 ' LastRow        Set MyRange4 = Worksheets("Main Tab").Range("A2:A" & LastRow)        Set MyRange5 = Worksheets("Main Tab").Range("AA2:AA" & LastRow)            myval4 = Application.WorksheetFunction.SumIf(MyRange4, Range("A" & j).Value, MyRange5)        Worksheets("Main Tab").Cells(j, 31).Value = myval4    Next    LastRowCorrection = Null    LastRowAvail = Null    VelocityLastRow = Null    LastRowData = Null    LastRow = Null    Application.ScreenUpdating = False    Application.DisplayStatusBar = False    Application.Calculation = xlCalculationManual    Application.EnableEvents = False    Application.CutCopyMode = False    LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Sheets("Main Tab").Range("AI2:AI" & LastRow).Formula = "=IF($E2="""","""",INDEX(Velocity!G:G,MATCH('Main Tab'!E2,Velocity!A:A,FALSE)))"    Sheets("Main Tab").Range("AR2:AR" & LastRow).Formula = "=IF($F2=0,0,ROUND((((($F2/$K2)/$J2)-$H2)/AI2),0.1))"    Sheets("Main Tab").Range("AH2:AH" & LastRow).Formula = "=IF($K2=0,0,IF($F2=0,0,ROUND((((($F2/$K2)/$J2)-$H2)/AI2)*$K2,0.1)))"    Sheets("Main Tab").Range("AG2:AG" & LastRow).Formula = "=IF(AH2<0,0,AH2)"    Sheets("Main Tab").Range("AK2:AK" & LastRow).Formula = "=IF($A2="""","""",1+CalculateWeek)"    Sheets("Main Tab").Range("AL2:AL" & LastRow).Formula = "=IF(AK2="""","""",CONCATENATE($E2,AK2))"    Sheets("Main Tab").Range("AM2:AM" & LastRow).Formula = "=INDEX(Velocity!C:C,MATCH('Main Tab'!AL2,Velocity!V:V,FALSE))"    Sheets("Main Tab").Range("AN2:AN" & LastRow).Formula = "=IF(K2=0,0,IF(AM2=0,0,ROUND((((($F2/$K2)*AM2)-$F2)-($H2-$F2))/AI2,0.1)))"    Sheets("Main Tab").Range("AO2:AO" & LastRow).Formula = "=IF(AN2="""","""",IF(AN2<0,0,AN2))"    Sheets("Main Tab").Range("AP2:AP" & LastRow).Formula = "=IF(AO2="""","""",AO2-AG2)"    Sheets("Main Tab").Range("AS2:AS" & LastRow).Formula = "=IF(K2<$AR$1,0,IF($AR2<0,"""",$AR2))"    Sheets("Main Tab").Range("AF2:AF" & LastRow).Formula = "=IF(K2>$AR$1,AS2,MAX(AG2,AO2))"    Sheets("Main Tab").Range("AU2:AU" & LastRow).Formula = "=IF(AE2<Parameters!$B$7,0,'Main Tab'!AA2)"    Sheets("Main Tab").Range("AT2:AT" & LastRow).Formula = "=CONCATENATE(A2,V2,AU2)"    Sheets("Main Tab").Range("AA2:AU" & LastRow).Value = Sheets("Main Tab").Range("AA2:AU" & LastRow).Value    Application.CutCopyMode = False    LastRowCorrection = Null    LastRow = Null    MainTabLastColum = Null    QuantityLastColumn = NullEnd SubSub GenDoc_Click()    DisableOptimize    LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    With ActiveWorkbook.Worksheets("Main Tab")            .AutoFilterMode = False                With .Range("A1:AU1")                     .AutoFilter                     .AutoFilter Field:=29, Criteria1:="<>C"                     .AutoFilter Field:=47, Criteria1:="<>0"                End With    End With    Set rData = Sheets("Main Tab").Range("A2:A" & LastRow) 'change this to suit your needs    Set rVis = rData.SpecialCells(xlCellTypeVisible)    rVis.Copy    Sheets("Order Upload").Select    Sheets("Order Upload").Range("A2").Select    ActiveSheet.Paste    LastRowUpload = Sheets("Order Upload").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Set rData = Sheets("Main Tab").Range("B2:B" & LastRow) 'change this to suit your needs    Set rVis = rData.SpecialCells(xlCellTypeVisible)    rVis.Copy    Sheets("Order Upload").Select    Sheets("Order Upload").Range("J2").Select    ActiveSheet.Paste    Set rData = Sheets("Main Tab").Range("S2:S" & LastRow) 'change this to suit your needs    Set rVis = rData.SpecialCells(xlCellTypeVisible)    rVis.Copy    Sheets("Order Upload").Select    Sheets("Order Upload").Range("B2").Select    ActiveSheet.Paste    Set rData = Sheets("Main Tab").Range("E2:E" & LastRow) 'change this to suit your needs    Set rVis = rData.SpecialCells(xlCellTypeVisible)    rVis.Copy    Sheets("Order Upload").Select    Sheets("Order Upload").Range("E2").Select    ActiveSheet.Paste    Set rData = Sheets("Main Tab").Range("AU2:AU" & LastRow) 'change this to suit your needs    Set rVis = rData.SpecialCells(xlCellTypeVisible)    rVis.Copy    Sheets("Order Upload").Select    Sheets("Order Upload").Range("F2").Select    ActiveSheet.Paste    Sheets("AOS Info").Select    LastRowAOS = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Sheets("Order Upload").Select    LastRowOrder = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    VelocityLastRow = Sheets("Velocity").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Range("C2:C" & LastRowOrder).Formula = "=ShpDt"    Range("D2:D" & LastRowOrder).Formula = "=PONumber"    Range("G2:G" & LastRowOrder).Formula = "=VLOOKUP(E2,'AOS Info'!$D$2:$E$" & LastRowAOS & ",2,FALSE)"    Range("H2:H" & LastRowOrder).Formula = "=VLOOKUP(E2,'Velocity'!$A$2:$G$" & VelocityLastRow & ",7,FALSE)"    Range("I2:I" & LastRowOrder).Formula = "=H2*F2"    Sheets("Order Upload").Range("C2:D" & LastRowOrder).Value = Sheets("Order Upload").Range("C2:D" & LastRowOrder).Value    Sheets("Order Upload").Range("G2:I" & LastRowOrder).Value = Sheets("Order Upload").Range("G2:I" & LastRowOrder).Value    Sheets("Order Upload").Range("K2:K" & LastRowOrder).Formula = "=IF(B2="""",LEFT(J2,1),B2)"    Sheets("Order Upload").Range("K2:K" & LastRowOrder).Value = Sheets("Order Upload").Range("K2:K" & LastRowOrder).Value    ActiveWorkbook.Worksheets("Order Upload").Sort.SortFields.Clear    ActiveWorkbook.Worksheets("Order Upload").Sort.SortFields.Add Key:=Range( _        "A2:A" & LastRowOrder), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _        xlSortNormal    ActiveWorkbook.Worksheets("Order Upload").Sort.SortFields.Add Key:=Range( _        "B2:B" & LastRowOrder), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _        xlSortNormal    With ActiveWorkbook.Worksheets("Order Upload").Sort        .SetRange Range("A1:K" & LastRowOrder)        .Header = xlYes        .MatchCase = False        .Orientation = xlTopToBottom        .SortMethod = xlPinYin        .Apply    End With    LastRowCorrection = Null    LastRowTransfer = Null    LastRowMain = Null    'SecondsElapsed = Round(Timer - StartTime, 2)    'MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation    'StartTime = 0    'Sheets("Main Tab").Select    'ActiveSheet.ShowAllDataEnd SubSub DisableOptimize()'Disable As Copying Reenable at end    Application.ScreenUpdating = False    Application.DisplayStatusBar = False    Application.Calculation = xlCalculationManual    Application.EnableEvents = FalseEnd SubSub EnableOptimize()'Disable As Copying Reenable at end    Application.ScreenUpdating = True    Application.DisplayStatusBar = True    Application.Calculation = xlCalculationAutomatic    Application.EnableEvents = True    Application.CutCopyMode = FalseEnd SubSub UnfilterAll()Dim ws As WorksheetOn Error Resume NextFor Each ws In ThisWorkbook.Worksheets    If ws.Visible Then ws.ShowAllDataNext wsOn Error GoTo 0End SubSub SkuDCSummary()    Worksheets("SKU-DC SUmmary").Select    DisableOptimize    'Dim StartTime As Double    'Dim SecondsElapsed As Double    'StartTime = Timer    Worksheets("Main Tab").AutoFilterMode = False    LastRowMain = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Sheets("Main Tab").Range("E1:E" & LastRowMain).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("SKU-DC Summary").Range("B1"), Unique:=True    Sheets("Main Tab").Range("U1:U" & LastRowMain).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("SKU-DC Summary").Range("O1"), Unique:=True    LastRowSKU = Sheets("SKU-DC Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    LastRowAOS = Sheets("AOS Info").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Sheets("SKU-DC Summary").Range("A2:A" & LastRowSKU).Formula = "=INDEX('Main Tab'!$C$2:$C$" & LastRowMain & ",MATCH(B2,'Main Tab'!$E$2:$E$" & LastRowMain & ",FALSE))"    Sheets("SKU-DC Summary").Range("C2:C" & LastRowSKU).Formula = "=INDEX('AOS Info'!$E$2:$E$" & LastRowAOS & ",MATCH(B2,'AOS Info'!$A$2:$A$" & LastRowAOS & ",FALSE))"    Sheets("SKU-DC Summary").Range("D2:D" & LastRowSKU).Formula = "=INDEX('AOS Info'!$B$2:$B$" & LastRowAOS & ",MATCH(B2,'AOS Info'!$A$2:$A$" & LastRowAOS & ",FALSE))"    Dim myval1 As Long    Dim MyRange1 As Range    Dim MyRange2 As Range    'Dim MyRange3 As Range    'Dim MyRange4 As Range    For j = 2 To LastRowSKU        Set MyRange1 = Worksheets("Main Tab").Range("U2:U" & LastRowMain)        Set MyRange2 = Worksheets("Main Tab").Range("F2:F" & LastRowMain)        'Set MyRange3 = Worksheets("Main Tab").Range("E2:E" & LastRowMain)        'Set MyRange4 = Worksheets("Main Tab").Range("E2:E" & LastRowMain)            myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("O" & j).Value, MyRange2)        Worksheets("SKU-DC Summary").Cells(j, 5).Value = myval1    Next    'Sheets("SKU-DC Summary").Range("E2:E" & LastRowSKU).Formula = "=SUMIFS('Main Tab'!$F2:$F$" & LastRowMain & ",'Main Tab'!$C2:$C$" & LastRowMain & ",A2,'Main Tab'!$E2:$E$" & LastRowMain & ",B2)"    Sheets("SKU-DC Summary").Range("F2:F" & LastRowSKU).Formula = "=E2/COUNTIFS('Main Tab'!$E2:$E$" & LastRowMain & ",B2,'Main Tab'!$H2:$H$" & LastRowMain & ","">0"")"    Sheets("SKU-DC Summary").Range("A2:F" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("A2:F" & LastRowSKU).Value    Sheets("SKU-DC Summary").Range("G2:G" & LastRowSKU).Formula = "=COUNTIFS('Main Tab'!$C2:$C$" & LastRowMain & ",A2,'Main Tab'!$E2:$E$" & LastRowMain & ",B2,'Main Tab'!$H2:$H$" & LastRowMain & ","">0"")"    For j = 2 To LastRowSKU        Set MyRange1 = Worksheets("Main Tab").Range("E2:E" & LastRowMain)        Set MyRange2 = Worksheets("Main Tab").Range("H2:H" & LastRowMain)        'Set MyRange3 = Worksheets("Main Tab").Range("E2:E" & LastRowMain)        'Set MyRange4 = Worksheets("Main Tab").Range("E2:E" & LastRowMain)            myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("B" & j).Value, MyRange2)        Worksheets("SKU-DC Summary").Cells(j, 8).Value = myval1 - Range("E" & j).Value    Next    'Sheets("SKU-DC Summary").Range("H2:H" & LastRowSKU).Formula = "=SUMIF('Main Tab'!$E$2:$E$" & LastRowMain & ",B2,'Main Tab'!$H$2:$H$" & LastRowMain & ")-E2"    'Sheets("SKU-DC Summary").Range("G2:H" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("G2:H" & LastRowSKU).Value    Sheets("SKU-DC Summary").Range("I2:I" & LastRowSKU).Formula = "=H2/G2"    Sheets("SKU-DC Summary").Range("J2:J" & LastRowSKU).Formula = "=E2/(H2+E2)"    Sheets("SKU-DC Summary").Range("I2:J" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("I2:J" & LastRowSKU).Value    'Sheets("SKU-DC Summary").Range("O2:O" & LastRowSKU).Formula = "=B2 & A2"    Sheets("SKU-DC Summary").Range("O2:O" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("O2:O" & LastRowSKU).Value    For j = 2 To LastRowSKU        Set MyRange1 = Worksheets("Main Tab").Range("U2:U" & LastRowMain)        Set MyRange2 = Worksheets("Main Tab").Range("AU2:AU" & LastRowMain)            myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("O" & j).Value, MyRange2)        Worksheets("SKU-DC Summary").Cells(j, 11).Value = myval1    Next    For j = 2 To LastRowSKU        Set MyRange1 = Worksheets("Main Tab").Range("U2:U" & LastRowMain)        Set MyRange2 = Worksheets("Main Tab").Range("AU2:AU" & LastRowMain)            myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("O" & j).Value, MyRange2)        Worksheets("SKU-DC Summary").Cells(j, 13).Value = myval1    Next    Sheets("SKU-DC Summary").Range("K2:O" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("K2:O" & LastRowSKU).Value    EnableOptimize    LastRowCorrection = Null    LastRowTransfer = Null    LastRowMain = Null    'SecondsElapsed = Round(Timer - StartTime, 2)    'MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation    'StartTime = 0End SubSub RunAll()Dim StartTime As Double    Dim SecondsElapsed As Double    StartTime = TimerCall Clear_ClickCall LoadMicroData2Call Calculate_ClickCall GenDoc_ClickCall SkuDCSummarySecondsElapsed = Round(Timer - StartTime, 2)    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation    StartTime = 0End Sub

• 2
  \$\begingroup\$Have you considered making changes and timing the results?\$\endgroup\$

  Stephen Rauch

  – Stephen Rauch

  2017-02-24 18:48:09 +00:00

  CommentedFeb 24, 2017 at 18:48
• \$\begingroup\$That's a good idea. I've done that for MatLab code back in school, but didn't even think about that being a functionality of Excel. It takes 2 hours to run in full for 500 lines, but I'll try timing each section and running it independently.\$\endgroup\$

  Emily Alden

  – Emily Alden

  2017-02-24 18:51:57 +00:00

  CommentedFeb 24, 2017 at 18:51
• 2
  \$\begingroup\$Excel 32-bit has explicit limitations on file size that 64-bit does not.\$\endgroup\$

  Raystafarian

  – Raystafarian

  2017-02-24 19:17:08 +00:00

  CommentedFeb 24, 2017 at 19:17
• 2
  \$\begingroup\$Especially in the loops, pull the worksheet data from the cells into amemory array instead of working directly on the worksheet. That will give you a huge speed-up in your code.\$\endgroup\$

  PeterT

  – PeterT

  2017-02-24 20:57:14 +00:00

  CommentedFeb 24, 2017 at 20:57
• 1
  \$\begingroup\$The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please seeHow to Ask for examples, and revise the title accordingly.\$\endgroup\$

  Jamal

  – Jamal

  2017-03-04 05:04:14 +00:00

  CommentedMar 4, 2017 at 5:04

1 Answer1

Start asking to get answers

Find the answer to your question by asking.

Explore related questions

See similar questions with these tags.