Excel 2019 - There isn't enough memory to complete this action

Question

I was seeing this error:

I see there are threads about this, but they're all locked.

Despite increasing the virtual memory to 16GB to match the amount of RAM the issue remained. Deleted all the links to other spreadsheets, issue remained. After upgrading the the 64-bit version of Office 2019 the issue remained, it still even recommended opening it in a 64-bit version!

After copying it to a server with 128GB RAM and Office 2019 64-bit and the issue remained, so I believe I was able to conclusively rule out an issue with memory!

Just wanted to say that the issue was that the Excel spreadsheet had somehow imported 1,048,576 rows and 16,384 columns to the bottom and side of the spreadsheet when one spreadsheet was linked to another, creating a spreadsheet with 17.2 billion mostly blank cells. The solution was a simple one, press Control+End to identify how many cells Excel thinks are in use, then copy and paste all the actual data into another spreadsheet and delete the original.

Perhaps Microsoft would like to create some sort of garbage collection where it deletes unused blank row and columns after the cells occupied by data?

Answer 1

Hi @Alex Fogerty ,
How about removing cells manually?
You can try this method:

1. select the "Range" you want to remove blank cells.
2. Click “Find & Select” in the “Editing” section of the “Home” tab and select “Go To Special…” on the drop-down menu.
3. On the “Go To Special” dialog box, select “Blanks” and click “OK.”
4. All the cells in the selection that are not blank are de-selected, leaving only the blank cells selected and delete them.

Try this method and let me know if this will help you.

---

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.

Answer 2

Hi Emi,

I have followed your instructions, but the moment I try to do anything with the table it locks up, giving me the "blue wheel of death" mouse cursor. I don't think it likes trying to work with 17.2 billion cells.

Answer 3

Excel 365 Pro Plus with Power Pivot.
Same problem everytime I use PP of any size.
Restarting Excel after every PP edit lets me continue.