Convert Formula to Value in Visible Cells Only

[neb255]

Hi, I need some help optimizing (or completely replacing) some code. I have a dataset across 12 columns and ~3k rows which have a combination of vlookup and sum formulas. After the vlookup is finished evaluating (5-10 seconds) I would like to replace the formulas for only these cells to be the value and leave the remaining cells with their Sum() formulas. After filtering out the rows with the Sum() in them I am left with ~31k cells of vlookups which I am trying to convert.

I have the below two versions of code which I pieced together after searching for this however both of these take a very long time to run. The first finishes in 8 min and 40 seconds, while the second at 10 min had only gotten through approximately 4500 cells.

I really hope that there is a faster way to do this, maybe by updating blocks of adjacent cells at the same time rather than going through each cell? But I cant seem to find a better way. Unfortunately I cannot hardcode the range since the columns and rows change on a fairly frequent basis, and I may not always need to convert all 12 columns at a time.

Any help is greatly appreciated.

Sub PasteValues()

Application.ScreenUpdating = False


Dim Rng As Range
Dim c1 As Range


Set Rng = Selection


For Each cl In Rng.SpecialCells(xlCellTypeVisible)
    cl.Value = cl.Value
Next cl


Application.ScreenUpdating = True


End Sub

Sub PasteValues()

Application.ScreenUpdating = False


Dim Rng As Range
Dim c1 As Range


Set Rng = Selection


For Each cl In Rng.SpecialCells(xlCellTypeVisible)
    cl.Copy
    cl.PasteSpecial xlValues
Next cl


Application.ScreenUpdating = True


End Sub

 

[Peter_SSs]

I think you could dramatically reduce the run-time of your existing codes by also halting the sheet's calculation while the code runs. You are also on the right track of working in 'blocks'. Both ideas are included in this code that you could try in a copy of your workbook. I haven't tested it on quite as much data as you have but I believe should improve the time markedly.

Sub Paste_Values()
  Dim rng As Range, ar As Range

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Set rng = Selection
  For Each ar In rng.SpecialCells(xlVisible).Areas
    ar.Value = ar.Value
  Next ar
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

 

[neb255]

I think you could dramatically reduce the run-time of your existing codes by also halting the sheet's calculation while the code runs. You are also on the right track of working in 'blocks'. Both ideas are included in this code that you could try in a copy of your workbook. I haven't tested it on quite as much data as you have but I believe should improve the time markedly.

Hi Peter, This was perfect! Code ran in just over 1 second. I dont know why I didnt think to turn off calculations, I always do that in the front-end when working with large data sets Thanks!

 

[Peter_SSs]

Hi Peter, This was perfect! Code ran in just over 1 second. I dont know why I didnt think to turn off calculations, I always do that in the front-end when working with large data sets Thanks!

Good news! Thanks for letting us know.