Solved

Excel Hangs / Not enough Memory

Posted on 2016-09-15
5
64 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
Comment Utility
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 45

Expert Comment

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

Expert Comment

by:CompProbSolv
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Synchronize a new Active Directory domain with an existing Office 365 tenant
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Microsoft Office Picture Manager has a Picture Shortcuts pane that shows a list with the Recently Browsed folders. While creating my video Micro Tutorial here at Experts Exchange showing How to Install Microsoft Office Picture Manager in Office 2013…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now