• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Excel Hangs / Not enough Memory

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?
1 Solution
Lee W, MVPTechnology and Business Process AdvisorCommented:
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.
Martin LissOlder than dirtCommented:
Are there any macros or other VBA code involved?
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.
plokij5006Author Commented:
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.
Glenn RayExcel VBA DeveloperCommented:
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)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now