Can you please let me know how I can remove all formulas from a sheet but keep the results of calculations in excel VBA?
I have a sheet called map which has lots of calculation columns there now I would like to remove all of this formulas but still keep the result to save into a new sheet.
1. Select the cells with formulas you want to remove but keep results, press Ctrl + C keys simultaneously to copy the selected cells. 2. Then click Home > Paste > Values.
Way 1 (Courtesy @rdhs)
Sub Sample()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("DTMGIS")
ws.UsedRange.Value = ws.UsedRange.Value
End Sub
Way 2 Using Copy - PasteSpecial - Values
Sub Sample()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("DTMGIS")
With ws.UsedRange
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With
End Sub
Way 3 Using SpecialCells
Sub Sample()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Sheets("DTMGIS")
On Error Resume Next
Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rng Is Nothing Then
rng.Value = rng.Value
End If
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With