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

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
ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats
End If
Next Cell
Application.CutCopyMode = False
End Sub

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.