?
Solved

Excel Hangs / Not enough Memory

Posted on 2016-09-15
5
Medium Priority
?
143 Views
Last Modified: 2016-09-21
A user has exported a report from EPOS software to excel, the spreadsheet is around 10,000 rows and 30 columns in size. The aim is to delete columns, and then filter the results in a pivot table. However, when trying to do this, an error occurs saying there is not enough memory. The laptop has 8GB RAM and 4 is being used at most whilst using excel, Office 2016 32-bit is installed. No other user programs were running. How can I get around this?
0
Comment
Question by:plokij5006
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 96

Expert Comment

by:Lee W, MVP
ID: 41800019
Upgrade Office to 64 bit (uninstall 32, reinstall 64) and see if that helps.  If Excel needs more RAM, it needs to use 64 bit as 32 bit is limited.
1
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41800133
Are there any macros or other VBA code involved?
1
 
LVL 21

Expert Comment

by:CompProbSolv
ID: 41800383
I had a client this week getting an "not enough memory" error with an Excel spreadsheet that wasn't large at all.  After seeing the error message, the spreadsheet would come up.  I re-saved the spreadsheet under another name and the issue was resolved in the copy.  You may want to give that a try.
1
 

Author Comment

by:plokij5006
ID: 41801174
I've tried installing 64-bit today, no error messages this time it just hangs.
No macros or VBA, only excel filters.
User demonstrated to me that when she used a filter to delete 5000 of the 10000 rows it just hangs.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 41802416
I've seen that behavior before (Excel hangs or is very slow when deleting filtered rows), but not with that low a quantity.  The issue happens when the rows to be removed are interspersed throughout the entire table.  If those items can be separated first (either by deletion or sorting), the process is tremendously faster.

Couple of workarounds:
1) Filter to show the data that is to be removed.  DELETE the data by highlighting and pressing [Delete].  Then unfilter and re-sort the entire table.
This is best if you know how your original data is sorted or you don't care.  One side effect is that the active workspace will still be defined to the last original row, but that usually isn't an issue.

2) Add a temporary column.  Fill it incrementally (1,2,3,.....) to provide an index. Filter to show the data that is to be removed.  Replace the temporary index values with "X" or "DELETE".  Unfilter and re-sort the data by the temporary column, then delete the column.  The rows to delete will be at the bottom of the data; select them and delete the rows.
This is better if you want to preserve the original order, but don't know the sort (or don't want to re-sort)


-Glenn
1

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question