Loop for setting cell values equal to themselves

[ugabrew]

Hi everyone,
I have a very large workbook that includes a lot of large formulas (approx 40mb). As such, after I update the formulas, I copy/paste values and save the values-only file for distribution. The code I am using is fairly simple (I thought), but is giving me a "Run-time error '1004': Application-defined or object-defined error". Here is my code:

s = EndRow - StartRow + 1 
For i = StartRow To EndRow 
    Range(Cells(i, 1), Cells(i, EndFormulaCol)).Value = Range(Cells(i, 1), Cells(i, EndFormulaCol)).Value 
    If i Mod 10 = 0 Then 
        Application.StatusBar = "Progress: " & Format((i - 10) / s, "Percent") & " completed." 
        DoEvents 
    End If 
Next i 
Application.StatusBar = False

The code is breaking on the main action line of "Range(Cells(i, 1), Cells(i, EndFormulaCol)).Value = Range(Cells(i, 1), Cells(i, EndFormulaCol)).Value". I have tried it with and without the "s" variable and the Application.StatusBar section that displays the macros progress as a percent of rows completed. Disabling that section doesn't seem to make a difference. The macro seems to break on the same row everytime, but if I change the data slightly it will break on a different row (and continue to break on that row each time). The .value = .value action seems pretty simple and works fine for 1000+ rows until it breaks. Is there some sort of memory issue that could be causing this? Thanks for the help!

Cross-posted here: http://www.ozgrid.com/forum/showthread.php?t=193619

[shg]

Why not just distribute a PDF?

[ugabrew]

The file includes data that others will sort/filter/resort for their needs, so unfortunately a PDF will not work.

[ugabrew]

I tested this a few different ways:

- Looping row by row
- Looping cell by cell (from left to right, then proceed to the next row)
- Looping through the sheet using ranges of 100 rows at a time

All of these seem to break on the same row, with the 3rd method of 100 rows at a time breaking with the 100 row group containing this row. When I looped cell by cell, it keeps breaking on the same cell. I tried to see what is different about that cell, but it looks like all the others. The strange thing is that my cell by cell will break on this cell, but when I open the debugger and press F8 to run that line of code again, it works... but then the next cell breaks. I press F8 and that one works too. So basically it gets to a point where it will not run automatically, but if I use the debugger and retry that line, it will execute in 1 or 2 attempts.

I'm really confused on this one. Could it be a memory issue, or something else behind the scenes that is preventing the code from running automatically beyond a certain point? I watched the task manager while it ran and the memory usage goes down while it runs, which is what I would expect since it is replacing long formulas with values... until it breaks.

[StephenR]

Cross post http://www.ozgrid.com/forum/showthread.php?t=193619

[ugabrew]

Sorry about that. I just read the rule that I should have provided a link. I've edited the OP to include that.

[shg]

Got merged cells?

[ugabrew]

Nope, no merged cells in this range.

[shg]

The macro seems to break on the same row everytime,

Can you duplicate that problem in a small workbook containing the line that fails?