您的文件必须是.xlsm文件。添加一个ActiveX按钮。双击它。在创建的button_click（）sub中添加一行：call doTheJob -粘贴以下代码后：

Private Sub doTheJob()
   Dim rng As Range, rw As Long, c As Long
   If TypeName(Selection) = "Range" Then
      If MsgBox("Change formulas with Values in selected range?", vbDefaultButton2 + vbExclamation + vbYesNo) = vbYes Then
         Set rng = Selection
         rw = rng.Rows.CountLarge
         Application.ScreenUpdating = False
         For c = 1 To rw
            If Trim(rng.Cells(c, 2).Value) <> "" Then
               rng.Rows(c).Copy
               rng.Rows(c).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            End If
         Next
         Application.CutCopyMode = False
      End If
   End If
End Sub

在单击按钮之前，您必须选择您感兴趣的范围，以将公式替换为值。

Private Sub doTheJob()
   Dim rng As Range, rw As Long, c As Long
    If MsgBox("Change formulas with Values in selected range?", vbDefaultButton2 + vbExclamation + vbYesNo) = vbYes Then
     Set rng = Me.ListObjects("NameOfTable").Range
     rw = rng.Rows.CountLarge
     Application.ScreenUpdating = False
     For c = 1 To rw
        If Trim(rng.Cells(c, 2).Value) <> "" Then
           rng.Rows(c).Copy
           rng.Rows(c).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        End If
     Next
     Application.CutCopyMode = False
    End If
End Sub

当范围是表时使用此代码。将“NameOfTable”修改为表的真实的名称。