How To Remove Formula In Excel And Keep Values Vba

by

To remove formulas in Excel and keep only the values using VBA, you can use the following code: 

    
      Sub RemoveFormulas()
          Dim ws As Worksheet
          Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your worksheet name

          With ws.UsedRange
              .Value = .Value
          End With
      End Sub

Let’s break down the code and understand how it works:

  • Sub RemoveFormulas() – This line defines the start of a VBA subroutine called “RemoveFormulas”. You can change the name to whatever you prefer.
  • Dim ws As Worksheet – This line declares a variable named “ws” of the type “Worksheet”.
  • Set ws = ThisWorkbook.Worksheets("Sheet1") – This line assigns the worksheet named “Sheet1” to the variable “ws”. Replace “Sheet1” with the actual name of the worksheet where you want to remove formulas.
  • With ws.UsedRange – This line starts a “With” block that allows us to work with the used range of the specified worksheet.
  • .Value = .Value – This line replaces all formulas in the used range with their corresponding values. By assigning the “Value” property to itself, Excel automatically converts the formulas to values.
  • End With – This line marks the end of the “With” block.
  • End Sub – This line marks the end of the VBA subroutine.

Once you have the code ready, you can execute it by following these steps:

  1. Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
  2. Insert a new module by clicking on “Insert” in the top menu and selecting “Module”.
  3. Paste the code into the newly created module.
  4. Modify the worksheet name if necessary (e.g., change “Sheet1” to your desired worksheet).
  5. Close the VBA editor and return to your Excel workbook.
  6. Press ALT + F8 to open the “Macro” dialog box.
  7. Select the “RemoveFormulas” macro from the list and click on the “Run” button.

The macro will then remove all formulas in the specified worksheet and keep the values. Make sure to save your workbook before running the macro, as it will overwrite any existing formulas with their values.

Same cateogry post

Leave a comment