Excel Locking Frequently

[Hijrofoyle]

hello, I'm working with an extremely large array of data (26 columns * 30,000 data points) and my program frequently locks on me, for no apparent reason. any help would be greatly appreciated

 

[DonkeyOte]

Put it in Access ( ) - Excel is crap at holding mass data...not built for it unfortunately.

Obviously not the answer you were looking for...but...

 

[Hijrofoyle]

Thanks. After that useless bit of data my post is off the "zero replies" list, so anyone that's looking to help me won't find it.

 

[DonkeyOte]

Whys is my Excel locking? I have 26 columns and 30000 rows? Doh.

If you are using macros ensure that you have

Application.ScreenUpdating = False
Application.Calculation = xlManual

Close all other applications as Excel will need all the memory it can get.

 

[Corticus]

Thanks. After that useless bit of data my post is off the "zero replies" list, so anyone that's looking to help me won't find it.

Lasw10 is correct, his information is not useless, Excel does not do well with massive data sets. But just to assure you that your post doesn't have to be on the zero replies list to get a response:

Try copying and pasting the poulated cells to a new workbook. This eliminates unnecessary formatting that may be eating up your memeory and making Excel crash. Also do as las suggested and turn of manual calculations.

Does your workbook contain lots of array formulas, these can cause problems as well.

HTH,
Corticus

 

[walkinjo]

What are you trying to do with your data? I use even larger data sets than that all the time, and Excel seems to lock up all the time. However, when I pull up task manager, I see that the CPU is just maxed out. Give it time, and eventually it finishes its calculations.

To avoid frequent re-calculation on the data, turn off auto-calculation (Tools -> Options, Calculation tab, set calculation to manual).

If you have a lot of formulas in the data, try replacing the formulas with their actual values so your sheet does not have to constantly re-calculate. After it has finished calculating once, just highlight all the formulas and hit CTRL-C, then Edit -> Paste Special, select Values, and hit OK. If you have some valuable formulas in there, make sure you store those in some other place before doing this.

Does that fix your problem?

 

[Hijrofoyle]

that's perfect waslkin jo, thanks alot.