How Can I Quickly Zap the Formulas in a cell range?

When I was working as a Reporting Analyst, I was responsible for generating the monthly EMEA Delivery Management scorecards, whose results would get discussed in a day-long meetings between all senior management members

One of the major issues with these scorecards when I inherited them was that there was no consistent way of regularly feeding all the relevant data, consequently the error rate was very high.

To address the issue, I developed the so-called consolidated feed where all the necessary indicators would be fed through getpivotdata() excel formulas at the time of their generation. That feed would be subsequently used as a sole input for the scorecards.

This, however, meant that whenever there was a change in fiscal quarter, I would have to make sure that I’ve got my QTD numbers copied and pasted as values to be available as a Q1, Q2, Q3 or Q4 references

Since the different scorecard indicators would get generated at different times during the month, I would only need to zap the QTD formulas of the indicators that were already complete.

Copying and pasting as values for the different ranges could be a very tedious exercise, so I came up with two approaches of tackling the task:

Approach 1: select formulas to scrap based on user input

	Sub ZapFormulaValuesUserInput()
	Dim MyRange As Range
	Dim Cell As Range
	Dim prv As Variant
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'written by Angelina Teneva, September 2016
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	With ActiveSheet
	On Error GoTo handler
	Set MyRange = Application.InputBox(Prompt:="Please Select a Range", _
	Title:="Choose Range to convert to values", Type:=8)
	For Each Cell In MyRange.SpecialCells(xlCellTypeVisible)
	If Not IsEmpty(Cell) = True Then
	prv = Cell.Value
	If Cell.HasFormula = True Then Cell.Value = prv
	End If
	Next Cell
	handler: MsgBox ("Operation Cancelled or Completed")
	End With
	End Sub

view raw CellInR_ZapFormulasUserInput.bas hosted with ❤ by GitHub

Whenever run, the code will basically ask you to select the range of cells whose formulas you would like to have scrapped.

Then, it will check whether each cell part of the specified range contains a formula or not. If a formula is present, it will just replace the formula with its previously generated value.

Repeating this over and over again for multiple values in a single column could, of course, also get quite frustrating. An alternative approach then would be to:

Approach 2: scrap the formulas in all cells that are not empty in a given column

	Sub ZapValuesNoUserInput()
	Dim Cell As Range
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'written by Angelina Teneva, September 2016
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'paste special as values
	For Each Cell In ActiveSheet.Range("AB2:AB" & ActiveSheet.UsedRange.Rows.Count)
	On Error Resume Next
	If Cell.Value <> "0" And Len(Cell) > 1 Then
	Cell.Activate
	ActiveCell.Copy
	ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats
	End If
	Next Cell
	Application.CutCopyMode = False
	End Sub

view raw CellInR_ZapFormulasCellValue.bas hosted with ❤ by GitHub

Happy VBA coding!