Solved

Excel Hangs / Not enough Memory

Posted on 2016-09-15
5
93 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
5 Comments
 
LVL 95

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 46

Expert Comment

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

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 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

773 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