Share via

Excel cannot complete this task with available resources. Choose less data or close other applications.

Anonymous
2018-09-30T03:44:36+00:00

Referring to following coding, when I run following macro, error message would occur after a few step.

I already select small range 5000 row instead of 50000 row at once, but still get following error message

Error Message : Excel cannot complete this task with available resources. Choose less data or close other applications.

Based on "The maximum selected range in a calculation is 2,048", do I exceed the limit, would it be possible to create a loop for every 150 row to finish the following tasks?

    Range("A1:L1").Select

    Selection.AutoFill Destination:=Range("A1:L150"), Type:=xlFillDefault

    Range("A1:L150").Calculate

    Range("A2:L150").Select

    Selection.Copy

    Range("A2").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Range("A151:L151").Select

    Selection.AutoFill Destination:=Range("A151:L300"), Type:=xlFillDefault

    Range("A151:L300").Calculate

    Range("A152:L300").Select

    Selection.Copy

    Range("A152").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 ...

    Range("A49851:L49851").Select

    Selection.AutoFill Destination:=Range("A49851:L50000"), Type:=xlFillDefault

    Range("A49851:L50000").Calculate

    Range("A49852:L50000").Select

    Selection.Copy

    Range("A49852").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Does anyone have any suggestions?

Thanks in advance for any suggestions

Sub Data_Loading()

    (Step 1)

    Range("A1:L1").Select

    Selection.AutoFill Destination:=Range("A1:L5000"), Type:=xlFillDefault

    Range("A1:L5000").Calculate

    Range("A2:L5000").Select

    Selection.Copy

    Range("A2").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    (Step 2)

    Range("A5001:L5001").Select

    Selection.AutoFill Destination:=Range("A5001:L10000"), Type:=xlFillDefault

    Range("A5001:L10000").Calculate

    Range("A5002:L10000").Select

    Selection.Copy

    Range("A5002").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    (Step 3)

    Range("A10001:L10001").Select

    Selection.AutoFill Destination:=Range("A10001:L15000"), Type:=xlFillDefault

    Range("A10001:L15000").Calculate

    Range("A10002:L15000").Select

    Selection.Copy

    Range("A10002").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    (Step 4)

    Range("A15001:L15001").Select

    Selection.AutoFill Destination:=Range("A15001:L20000"), Type:=xlFillDefault

    Range("A15001:L20000").Calculate

    Range("A15002:L20000").Select

    Selection.Copy

    Range("A15002").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    (Step 5)

    Range("A20001:L20001").Select

    Selection.AutoFill Destination:=Range("A20001:L25000"), Type:=xlFillDefault

    Range("A20001:L25000").Calculate

    Range("A20002:L25000").Select

    Selection.Copy

    Range("A20002").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    (Step 6)

    Range("A25001:L25001").Select

    Selection.AutoFill Destination:=Range("A25001:L30000"), Type:=xlFillDefault

    Range("A25001:L30000").Calculate

    Range("A25002:L30000").Select

    Selection.Copy

    Range("A25002").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    (Step 7)

    Range("A30001:L30001").Select

    Selection.AutoFill Destination:=Range("A30001:L35000"), Type:=xlFillDefault

    Range("A30001:L35000").Calculate

    Range("A30002:L35000").Select

    Selection.Copy

    Range("A30002").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    (Step 8)

    Range("A35001:L35001").Select

    Selection.AutoFill Destination:=Range("A35001:L40000"), Type:=xlFillDefault

    Range("A35001:L40000").Calculate

    Range("A35002:L40000").Select

    Selection.Copy

    Range("A35002").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    (Step 9)

    Range("A40001:L40001").Select

    Selection.AutoFill Destination:=Range("A40001:L45000"), Type:=xlFillDefault

    Range("A40001:L45000").Calculate

    Range("A40002:L45000").Select

    Selection.Copy

    Range("A40002").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    (Step 10)

    Range("A45001:L45001").Select

    Selection.AutoFill Destination:=Range("A45001:L50000"), Type:=xlFillDefault

    Range("A45001:L50000").Calculate

    Range("A45002:L50000").Select

    Selection.Copy

    Range("A45002").Select

    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False

    Range("A1").Select

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-09-30T08:19:25+00:00

    ok Thanks

    Was this answer helpful?

    0 comments
  2. Anonymous
    2018-09-30T07:00:05+00:00

    The following code cannot solve this issue, it exceeds the maximum selected range in a calculation 2,048.

    Application.CutCopyMode = False     'Cancel the Copy held in clipboard

    Even through loop is created within range limits, but I still get the same error.

    Dim i As Long

    Dim StartR As Long

    Dim EndR As Long

            For i = 1 To 333

                StartR = Application.Max(0, (i - 1) * 150) + 1

                EndR = i * 150

                Range("A" & StartR & ":L" & StartR).AutoFill Destination:=Range("A" & StartR & ":L" & EndR), Type:=xlFillDefault

                Range("A" & StartR & ":L" & i * 150).Calculate

                Application.CutCopyMode = False

                Range("A" & StartR + 1 & ":L" & EndR).Copy

                Range("A" & StartR + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

                Application.CutCopyMode = False

                DoEvents

            Next i

        End If

    Do you have any suggestions on how to handle this situation?

    Thank you very much for any suggestions (^v^)

    Was this answer helpful?

    0 comments
  3. OssieMac 48,001 Reputation points Volunteer Moderator
    2018-09-30T06:13:19+00:00

    Try the code without selecting. It is almost never necessary to select data with VBA; just reference the range as per the example below.

    Insert Application.CutCopyMode = False  before and after the Copy and Paste to remove the Copy from clipboard.

    Insert DoEvents after each step to ensure that the step completes before continuing with the next step.

    I don't know for sure if it will help but worth a try. I have only edited the first 2 steps for you so you will need to edit the remainder of the code.

    The code could probably have been placed in a loop but try as per my example and see if it helps.

    Sub Data_Loading()

        '(Step 1)

        Range("A1:L1").AutoFill Destination:=Range("A1:L5000"), Type:=xlFillDefault

        Range("A1:L5000").Calculate

        Application.CutCopyMode = False    'Cancel the Copy held in clipboard

        Range("A2:L5000").Copy

        Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        Application.CutCopyMode = False    'Cancel the Copy held in clipboard

        DoEvents    'Ensure that all processes have completed before continuing.

        '(Step 2)

        Range("A5001:L5001").AutoFill Destination:=Range("A5001:L10000"), Type:=xlFillDefault

        Range("A5001:L10000").Calculate

        Application.CutCopyMode = False     'Cancel the Copy held in clipboard

        Range("A5002:L10000").Copy

        Range("A5002").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        Application.CutCopyMode = False     'Cancel the Copy held in clipboard

        DoEvents     'Ensure that all processes have completed before continuing.

    Was this answer helpful?

    0 comments