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

Nothing worked for this except delting the workbook and creating a fresh wb from the source. I must have corrupted it somehow.

Ask Your Question

Weekly Poll