Excel Won't Autofill Anymore

[therber2]

Hey guys,

I tried searching for this topic thinking it would be common...alas, here is the issue:

I have a large excel file. There is a sheet that databases NCAAB and has, currently, 12,229 rows and around 50 to 100 columns.
When I try to autofil the most recent line in the database down 1 row, 2 rows, several, 50 rows, etc. what happens is a long lag and then the program returns things to how the were before the attempt to autofill.

I've tried pasting 99.9% of the values. The only formulas exist in the very last row for when I insert new data (in order to crunch it out).
I've also tried doing that and saving the file.

Any ideas?

Thanks,
Tom

[therber2]

I should also mention that, if I attempt to do any sort of cut and pasting of columns or rows or deleting them, the program will crash completely.

Have I hit a data limit?
I've got tons of RAM and a solid CPU so I doubt this is the issue. With this file open and several other programs and a bunch of excel tabs, I am under 40% memory usage.

[newdoverman]

Your computer is working with probably over 1000000 cells. That is going to take some time to calculate.

If you haven't cleaned out temporary files in a while, try running a utility like Ccleaner from www.piriform.com and try again.

If you are working off external media like a thumb drive, move the file to the hard drive. Excel needs a lot of space to create a temporary file.

You might try turning off automatic calculations in favour of manual calculation until the auto fill is complete.

[therber2]

I tried all of those (or N/A) except for turning off automatic calc.

Will try that and report back.

Thanks so much.

[therber2]

The other problem is that I have about 6k rows hidden.
When I try to unhide them, the program crashes.

This worries me because I am wondering if it would ever be possible to recover that hidden data.

[therber2]

I tried all of those (or N/A) except for turning off automatic calc.

Will try that and report back.

Thanks so much.

Okay, that does not work.

*At this point, my goal is to retrieve the full database into a brand new excel file. No formulas or macros.
So the problem is, anything I try to do to get the data back, the program crashes. I have the one big sheet and then the 6k rows hidden.
Here is what will cause the program to crash:
1. Copying the sheet with hidden rows to
a. same workbook
b. new workbook
2. If I save the file as a new file and delete EVERYTHING other than the one sheet and do all of the same thing to get the hidden cells back. It still crashes.

Oy. Is there any sort of excel safe mode or is there a way to allocate more memory to the one sheet?

3. Tried restarting the computer. Making this file the only one open.

[newdoverman]

How large is this file. If not too large when zipped, can you upload it?

You can disable add ins via the Excel Options, Add ins.

Have you tried opening the file and then opening a second instance of Excel by holding down the Shift key and clicking the Excel icon. After doing this, select ranges of data and copying and then pasting into the new workbook?

[therber2]

Have you tried opening the file and then opening a second instance of Excel by holding down the Shift key and clicking the Excel icon.

Yes.

After doing this, select ranges of data and copying and then pasting into the new workbook?

Works, but the problem is you cannot copy hidden data to a new instance or even another sheet I believe.

[newdoverman]

You can copy with hidden data but the copy unhides the data if my experiments are anything to go by.

[therber2]

Hmm. Seems you're right. I could have sworn I tried that.

I remade the sheet...contains the same amount of formulas and date exactly; however, the program doesn't crash anymore.

Perhaps by deleting the old sheet, I killed a bunch of background web queries or something. Very odd

[newdoverman]

Thank you for the feedback. I'm glad for you that you got the workbook working as it should.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.