Excel 365 macro -- "There isn't enough memory to complete this action."

Dean Reynolds 21 Reputation points
2020-11-26T07:11:45.877+00:00

I keep getting this error message when trying to run a MACRO in an EXCEL file.
"There isn't enough memory to complete this action. Try using less data or closing other applications"

I am a basic excel user (self-taught).
I am using 64bt Excel from 365. My computer is 'new' and has 16gb of RAM (i7-8700T) and shows over 72GB of free storage space (in addition to other external drives and dropbox). A microsoft 'helper' made certain all programs are up to date and even ran a 'repair' file...but no luck. I also closed all other programs....and restarted computer...still no luck.

The particular excel file is ~180kb, has 570 rows & 39 columns of entered data & 14 columns of summarized and categorized data based upon the first 39 columns.

I am using MACROS to create and update various reports on separate 'tabs' using the data on the 'main' tab. Some simple MACROS work.....but most do not....and even those MACROS sometimes 'give the same error message.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,638 questions
0 comments No comments
{count} votes

Accepted answer
  1. Emi Zhang-MSFT 21,626 Reputation points Microsoft Vendor
    2020-11-27T08:20:46.493+00:00

    Hi @Dean Reynolds ,
    Did this problem only appear in Excel? How about Word?
    Did you install any add-ins for Excel? Try to disable all add-ins in Excel, confirm if you can record the macro and run it.


    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Dean Reynolds 21 Reputation points
    2020-11-27T22:15:12.147+00:00

    I have only noticed the problem with EXCEL. I don't use WORD very much. There are no add-ins enabled. I am still not able to record the macro and run it.

    Also noticed another problem.....I tried to copy the MAIN file into another tab, then modify it to prepare the needed report......however, when I tried to 'sort' using one of the columns*, I received another error message, "Run-Time error '9': Subscript out of range"

      *this column (and several other columns) are imbedded IF functions.
    

    All very frustrating....I thought I could simply record a Macro and it would run....

    Thank you for your support and assistance.

    0 comments No comments

  2. Dave Patrick 426K Reputation points MVP
    2020-11-27T22:22:12.137+00:00

    I'd work through this one.
    https://learn.microsoft.com/en-us/office/troubleshoot/excel/available-resources-errors

    --please don't forget to Accept as answer if the reply is helpful--

    0 comments No comments