Microsoft Outlook 2013 32/64-bit (mail m...

I'm using an Excel table created by another department (basic table, blue and white lines) but it's huge, over 600,000 lines of data and in columns L and M, I created vlookups for I could pull the data our department needed. Because management needed the source data, I had to keep the original worksheet and created others to show our separate data. Now I have a huge, lumbering 114.590kb workbook that is cumbersome to open, cannot be emailed, and heaven help you if you accidently click into columb L or M because those formulas wll seize and hold up the workbook for minutes at a time. I mean it locks up the workbook/Excel and you'll have to X out of it to free yourself.

My manager asked me to change the formulas to values but I canNOT do this, it just locks up on me and I'm trapped.

If it means anything, when I was putting in the formulas, I typed it into the top row and it autofilled the remainder of the rows. It looked awesome when it was happening, though it look 10-15 minutes to complete, but now, I cannot get rid of the formulas, even a single row at a time.

Has anyone worked with a problem like this? I mean it when I say there are over 600,000 lines of data and each row has columns to M so we're talking about more than 7 million cells of data.

1 - Open Excel to a blank workbook

2 - File...Options...Formulas

3 - Set Calculations Options to Manual, Uncheck Recalculate workbook before saving (just to be safe)

Manual calculation will now be set for the Excel instance, not just the new workbook.

4 - Open the "huge" workbook

5 - Nothing should calculate so you should be able to do whatever you want.

6 - Reset Calculation to Automatic before quitting Excel.or (I think this will work)

Sub MyValues() Range("L:M").Copy Range("L1").PasteSpecial Paste:=xlPasteValues End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History