Excel 2010 - Overflow Error when converting worksheet to values-only - VBA code improvement ?

Question

Worksheet of interest has data in columns A-J.  Columns K-BD contain formulas to draw info from other tabs (heavy VLOOKUP use).  K-BD are also pretty heavily conditionally formatted which cannot be altered.

Want to convert entire sheet from Formulas to Values-only, preserving all formatting (conditional and fixed).

Current VBA converts the entire worksheet from formulas to values-only using :

ActiveSheet.UsedRange.Cells.Value = Activesheet.UsedRange.Cells.Value.

This has worked fine until the working sheet contained > ~ 1100 lines.  Then, I get the "Run-time error '6':  Overflow"  error.

Error repeats after Excel completely closed & re-opened, and only 1 other application running (Outlook).

After 'Ending' the macro at the error box, I manually used CTRL-A, then paste-special (values only), at A1 and all worked just fine.  Grrrrr.

Looking for better, less memory intensive code to perform the Formulas -- > Values conversion.

Column by column ?    Row by row ?    Cell by cell ?

Columns A-BD for working sheet are fixed.  Rows involved are dynamic -- foresee 15-20K rows as future need to be processed.

Am VBA Newbie, and syntax for array or loop operations is pretty weak (at best).

Another thought (if this might be a factor)-- is it viable/feasible to suspend the Undo cache/stack/buffer, or the auto-backup-file-save functionality to alleviate the memory crunch ?

Running Excel 2010 on Windows 7 (Enterprise); Excel runs all four i5 processors w/ 4Gb RAM.  Working file on local, non-networked, drive.

Answer 1

Since you know your target area width, what about identifying the last used row on the page and doing your copy/paste operation on ranges of 500 lines at a time?

Setting calculation to manual first, of course.