vba 如何从工作表中删除公式但保留其计算值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20671795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to remove formulas from sheet but keep their calculated values
提问by Behseini
Can you please let me know how I can remove all formulas from a sheet but keep the results of calculations in excel VBA?
您能否让我知道如何从工作表中删除所有公式，但将计算结果保留在 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.
我有一个名为 map 的工作表，其中有很多计算列，现在我想删除所有这些公式，但仍保留结果以保存到新工作表中。
回答by Siddharth Rout
Way 1(Courtesy @rdhs)
方式 1（由@rdhs 提供）
Sub Sample()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("DTMGIS")
ws.UsedRange.Value = ws.UsedRange.Value
End Sub
Way 2Using Copy - PasteSpecial - Values
方式二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 3Using SpecialCells
方式三使用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
