Remove formula retain value (VBA)

Scenario

You have several cells on one worksheet that have formulas. You need to transfer these cells to another worksheet as value only. The "paste/special value" is not an option.

Solution

Using VBA in Excel we can achieve the solution by using the special cell's property called SpecialCells(xlCellTypeFormulas) and by using a simple FOR cycle.

The following is a simple subroutine we can use to copy only values from cells with formula to the same or another worksheet.

Sub RemFor()


Dim i As Integer


Dim c As Range


i = 1


For Each c In Cells.SpecialCells(xlCellTypeFormulas)


   Sheets("Sheet1").Cells(i, 3) = c.Value


   i = i + 1


Next c


End Sub

For example, we can use a simple worksheet, like in the following picture, when the cells from A1 to A6 are only integer values, and from B1 to B6 there's a simple SUM operation:

By using the above code (inserting it in ThisWorkbook space in Visual Basic Editor) and specifying Sheets("Sheet1").Cells(i, 3) = c.Value we will copy only the values of the B1:B6 range to C1:C6 range (the number 3 in Cells(i, 3) specify the column in which we'll transfer the values), on the same worksheet (Sheet1).

Remove all formulas retaining the values

However, if you just want to remove all formulas from a worksheet (to avoid some enduser just screw up data when opening and viewing it), just use the following code, that will replace all cells with formulas with their own values:

Sub RemFor()


Dim c As Range


For Each c In Cells.SpecialCells(xlCellTypeFormulas)


   Sheets("Sheet1").Cells(c.Row, c.Column) = c.Value


Next c


End Sub

NOTE: you'll have to specify on which worksheet you want remove all cell's formulas (Sheet1).

Finally, if you want to remove all cell's formulas only in ActiveSheet just use the following code:

Sub RemFor()


On Error Resume Next


Dim c As Range


For Each c In Cells.SpecialCells(xlCellTypeFormulas)


   ActiveSheet.Cells(c.Row, c.Column) = c.Value


Next c


End Sub