General area - excel question -- excel limitation

Question

0.00/5 (No votes)

See more:

My client works with excel constantly, especially with large data sets. Considering the spreadsheet contains 200,000 rows, sometimes there is a set of 8000 rows deleted at once. Initially, this was taking 2 minutes to perform this task, now it is taking 10 minutes. sometimes Excel completely stops responding, causing Excel to hang.

Would upgrading to this version of excel solve some of the issues at hand?
Excel 2010 Performance: Performance and Limit Improvements[^]

Any ideas is much appreciated!

Thanks!

What I have tried:

I tried deleting 8000 rows of data at one time in a sheet containing about 200,000 rows. This causes excel to hang, and requires me to restart excel program again.

Posted 17-Oct-16 8:02am

VK19

Updated 17-Oct-16 20:36pm

Patrice T

v3

Add a Solution

Comments

Maciej Los 17-Oct-16 14:09pm

No, it won't help. ;(

Suvendu Shekhar Giri 17-Oct-16 14:17pm

No, upgrading will not help much. You need to find a suitable approach.
What is the need to extract 200000 rows at a time?
Can not you create separate file based on month/category or some other constraint?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2016-10-17T20:36:00

Solution 2

You can :
1) Turn off auto calculate before deleting : How to Change the Automatic Calculation and Multi-Threading Features in Excel 2013[^]

2) Adding a deleted column and filtering on that instead of deleting the row.

3) Try a 64bit version of Excel.

Posted 17-Oct-16 20:36pm

Mehdi Gholam