Solved How do I easily change formula to value in very large table?

Microsoft Outlook 2013 32/64-bit (mail m...
March 6, 2018 at 13:53:01
Specs: Windows 64
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.

See More: How do I easily change formula to value in very large table?

Reply ↓  Report •

March 6, 2018 at 15:06:18
✔ Best Answer
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("L1").PasteSpecial Paste:=xlPasteValues
End Sub

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

Reply ↓  Report •
Related Solutions

Ask Question