# Convert Formula to Value in Visible Cells Only

#### neb255

##### Board Regular
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.

Code:
``````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``````

Code:
``````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``````

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Peter_SSs

##### MrExcel MVP, Moderator
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.

Code:
``````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

##### Board Regular
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

##### MrExcel MVP, Moderator
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.

Replies
5
Views
157
Replies
29
Views
288
Replies
4
Views
85
Replies
1
Views
57
Replies
5
Views
115