?
Solved

Excel Hangs / Not enough Memory

Posted on 2016-09-15
5
Medium Priority
?
194 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 97

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 50

Expert Comment

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

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 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