Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Formulas to Values - VERY slow

    In a macro I populate a block of data with formulas, then Application.CalculateFull and it only takes a couple of seconds. However, when I then convert the formulas to values my system effectively hangs. The CPU goes up to around 30% and stays there. It happens regardless of whether I code it as .Copy then .PasteSpecial xlPasteValues or as .Value = .Value. The block is not that large - some 13000 rows by 20 columns. And even if I break it down to two columns at a time, with an Application.CutCopyMode = False in between, it still acts the same.
    Curiously, if I step thru the code it's fine. I've seen similar behavior before - and the solution then was to uncheck a setting in the clipboard pane. I looked at that and it's fine.
    Anybody have any ideas or any troubleshooting hints?
    TIA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try turning off autocalc before running and turning it back on afterwards.
    Application.Calculation = xlManual
    'rest of code here
    'At end
    Application.Calculation = xlAutomatic

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    Thanks, Steve. I do already have it set to xlCalculationManual. As you probably do, I also set ScreenUpdating to False at the beginning of each macro and reset everything before exiting.
    When I looked again, I was wrong about it working fine when I stepped thru the code. The same phenomenon occurred. What's odd is that it's reasonably fast copying in and then calc'ing the formulas. I would have expected that to be the problem if the block of data were too large. Once the values have been determined, you'd think converting from formulas to values would be the easy (i.e., quick) part. Evidently not...
    I changed it to do two columns at a time - including copying in the formulas - and it's much quicker. So my code is something like
    Range("rngSource").Copy
    With Range("rngDest")
    .PasteSpecial xlPasteFormulas
    .Calculate
    .Copy
    .PasteSpecial xlPasteValues
    End With
    I don't understand why that would be better, but there you go.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Colin,

    See if this works any better:
    Code:
    Sub Test()
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim Rng As Range
    For Each Rng In Range("rngSource").SpecialCells(xlCellTypeFormulas)
      Rng.Value = Rng.Value
    Next
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    End Sub
    Last edited by macropod; 2011-04-17 at 21:52. Reason: Edited code to refer to"rngSource".
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •