Convert Formula to Value in Visible Cells Only

neb255

Board Regular
Joined
Sep 14, 2011
Messages
60
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
 

Some videos you may like

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
Joined
May 28, 2005
Messages
46,276
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 14, 2011
Messages
60
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
Joined
May 28, 2005
Messages
46,276
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,250
Messages
5,527,635
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top