none
Sumifs taking very long time RRS feed

  • Question

  • HI,

    I tried kind recording Macro for the SUMIFS, however, it's taking very very long time to run the report, just to summarize, there are total three macros two macros to lookup the values based on the specific criteria in a table (From Magento and OMS workbooks) and the 3rd Marco to run the reconciliation.

    While running the 3rd Macro the excel is almost getting stuck. I have data which goes more than 200,000 lines or more

    Is there any suggestions to improve the code.

    kindly advise.

    regards

    <g class="gr_ gr_43 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="43" id="43">aleem</g>

    Option Explicit
    Sub Reco()
    '
    ' Reco Macro
    '
    
    '
        
        Application.ScreenUpdating = False
        Windows("Magento.xlsx").Activate
        Range("C:C,J:J,N:N").Select
        Range("N1").Activate
        Range("C:C,J:J,N:N,AI:AI").Select
        Range("AI1").Activate
        Selection.Copy
        Windows("Reconciliation File.xlsm").Activate
        Sheets("Magento").Select
        Columns("A:A").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A5").Select
        Application.CutCopyMode = False
        Selection.Copy
        Columns("A:A").Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A6").Select
        Windows("OMS.xlsx").Activate
        ActiveWindow.ScrollColumn = 19
        Range("D:D,E:E,U:U,X:X").Select
        Range("X1").Activate
        Selection.Copy
        Windows("Reconciliation File.xlsm").Activate
        Sheets("OMS").Select
        Columns("A:A").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A2").Select
        Application.CutCopyMode = False
        Selection.Copy
        Columns("A:A").Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A5").Select
        Sheets("Magento").Select
        Columns("A:B").Select
        Selection.Copy
        Sheets("Reconciliation").Select
        Columns("A:A").Select
        ActiveSheet.Paste
        Sheets("OMS").Select
        Range("A2:B2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlUp)).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Reconciliation").Select
        Range("A2").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Columns("A:B").Select
        Application.CutCopyMode = False
        ActiveSheet.Range("$A$1:$B$100000").RemoveDuplicates Columns:=Array(1, 2), _
            Header:=xlNo
        Range("C3").Select
        ActiveCell.FormulaR1C1 = _
            "=SUMIFS(Magento!C4,Magento!C2,Reconciliation!RC2,Magento!C3,Reconciliation!R2C)"
        Range("C3").Select
        Selection.Copy
        Range("C3:G3").Select
        ActiveSheet.Paste
        Range("H3").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=SUMIFS(OMS!C4,OMS!C2,RC2,OMS!C3,R2C)"
        Range("H3").Select
        Selection.Copy
        Range("H3:L3").Select
        ActiveSheet.Paste
        Range("C3:L3").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("B3").Select
        Selection.End(xlDown).Select
        Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).Select
        Range(Selection, Selection.End(xlUp)).Select
        ActiveSheet.Paste
        ActiveWindow.SmallScroll down:=-140
        Cells.Select
        Application.CutCopyMode = False
        Selection.Copy
        Columns("A:L").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("M2").Select
    End Sub
    

    • Edited by aleemATG Saturday, April 14, 2018 10:30 AM
    Saturday, April 14, 2018 10:29 AM

All replies

  • Hi aleemATG,

    I can see in code that on so many lines unnecessary steps were recorded, Like just selecting the range again and again.

    Or selecting one range then select any other range then again select any other range.

    So executing this repetitive steps with 2,00,000 rows will consume lots of time.

    Also in recorded macro, Not all the steps get recorded. Excel will not record some steps which you need to modify it later as per your requirement.

    So I suggest you to try to understand your recorded macro and try to developed your own macro based on that.

    It will help you to improve the performance.

    Reference:

    WorksheetFunction.SumIfs Method (Excel)

    Object model (Excel VBA reference)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 16, 2018 5:27 AM
    Moderator
  • Hi aleemATG,

    Is your issue solved?

    I find that you did not follow up this thread after posting the issue.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 27, 2018 9:17 AM
    Moderator