How to remove macros from Excel (A complete guide)
Microsoft Excel offers a powerful feature in the form of macros, which can automate repetitive tasks, and accomplish many operations that built-in Excel features cannot achieve. However, there are instances where these macros might become redundant or even pose potential security risks, especially when sharing your workbook with others. For these moments, understanding how to safely and effectively remove macros becomes essential. This guide is crafted to walk you through the varied methods of removing macros from your Excel workbooks, ensuring you have a clean, streamlined file when needed.
When opening a macro-enabled workbook and attempting to remove the macros, you must first click the Enable Content button in your workbook. This is necessary before you can proceed to delete them.
Note:If you don't see this button, it's a green light for you to go ahead and delete the macros. The reason for its absence might be if you've created a workbook embedded with macros yourself and saved it. When reopening that workbook on the same computer and with unchanged Excel settings, the Enable Content button often doesn't show up. That's because the file comes from a trusted source (you) and is located in a trusted location (your computer).
Please remember that once a macro is removed, recovery is not possible. To safeguard your work, it's advisable to create a backup copy of your workbook before deleting any macros.
Remove a specific macro
Step 1: Open the Macro dialog box
Navigate to the Developer tab and select Macros to open the Macro dialog box. (If the Developer tab is not visible in your Excel, see the note after the steps.)
If you're using Excel 2013 or a more recent version, the Macros button can also be found within the View tab.
Shortcut enthusiasts can directly press Alt + F8 to access the Macro dialog box.
Step 2: Select and delete the specific macro
Expand the "Macros in" drop-down menu and select the desired destination from where to remove macros.
From the list of macros, select the one you want to remove.
To add the Developer tab to the ribbon in Excel, do as follows:
Right-click anywhere on the ribbon and select Customize the Ribbon….
In the list of Main Tabs on the right side of the window that appears, check the box next to Developer and click OK.
If you intend to remove multiple macros, you'll have to repeat the above process for each individual macro. For instructions on batch-deleting all macros, proceed to the following sections.
Delete all macros from a workbook
In cases where you need to remove all macros from a workbook, here are two methods:
Delete all macros from a workbook by saving the file in xlsx format
The "Excel Workbook (*.xlsx)" format inherently lacks support for macros. As such, saving your workbook in this specific format will result in the automatic removal of all embedded macros. To execute this method, kindly proceed as follows:
Navigate to File > Save As.
Expand the file type drop-down menu and choose the "Excel Workbook (*.xlsx)" format.
Tip: You can enter a new name for the .xlsx file in the textbox above the drop-down menu. To save the file in another location, click Browse.
Note: The Save As interface might differ across various Excel versions. If you don't observe the right pane as shown above, where file format selection is available, you can always click Browse and then choose the "Excel Workbook (*.xlsx)" format.
In the prompt box that appears, select Yes.
As a result, a new file in ".xlsx" format without macros is saved. This .xlsx file will adopt the name of the original .xlsm file and be saved in the same location.
Note: This method does not excise UserForms, Excel 5/95 dialog worksheets, and similar elements. Should you aim to eliminate those, kindly refer to the next method.
Delete all macros from a workbook with a one-click option provided by Kutools
Kutools for Excel, an advanced Excel add-in, provides users with a one-click option to effortlessly delete all macros embedded in a workbook. With a single click, you can purge VBA modules, UserForms, Excel 5/95 dialog sheets, and Excel 4 XLM macro worksheets.
Once you have opened the workbook containing macros you want to delete, navigate to the Kutools tab, and select Delete > Remove All Macros. And voilà, you're done!
Note: Want to access this feature? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day trial. Don't wait, give it a try today!
Delete all macros from multiple workbooks
When faced with the task of eliminating macros from several workbooks, there are two approaches you can take:
Remove all macros from workbooks in a folder with VBA
In this section, I will demonstrate how to efficiently remove macros from all workbooks within a designated folder using a VBA macro.
Note: Before removing macros with a VBA macro, you need to:
Navigate to File > Options > Trust Center > Trust Center Settings > Macro Settings, then select the "Trust access to the VBA project object model" option.
Ensure no workbooks in the designated folder are open when executing this VBA. Running it with open workbooks might lead to errors.
Step 1: Create a new module
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
Click Insert > Module to create a new module.
Step 2: Copy VBA code to module window
Copy the below VBA code and paste it to the opened Modulewindow.
' Update by ExtendOffice
Dim wb As Workbook
Dim FolderPath As String
Dim filename As String
Dim VBComp As Object
Dim VBProj As Object
.title = "Select a folder"
If .SelectedItems.Count = 0 Then
MsgBox "No folder selected. The procedure will exit.", vbExclamation
FolderPath = .SelectedItems(1)
If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath + "\"
filename = Dir(FolderPath & "*.xls*")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Do While filename <> ""
Set wb = Workbooks.Open(FolderPath & filename)
If wb.HasVBProject Then
Set VBProj = wb.VBProject
For Each VBComp In VBProj.VBComponents
filename = Dir
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Macros removal completed!", vbInformation
Step 3: Run the VBA code
In the Modulewindow, press F5 or click the button to execute the pasted code.
In the Select a folder window that appears, select the folder containing workbooks you will remove macros from, and click OK.
After the macro has finished processing all Excel files in the selected folder and removing the macros from them, you will see a "Macros removal completed!" message box.
This method does not excise UserForms, Excel 5/95 dialog worksheets, and similar elements. Should you aim to eliminate those, kindly refer to the next method.
Activating the Trust access to the VBA project object model option can pose a security risk. It's advised to enable the option only while running this code. Ensure you deselect the "Trust access to the VBA project object model" option once the code completes its execution.
Remove all macros from any specific workbooks with Kutools
Kutools for Excel provides a user-friendly way to remove all macros from multiple workbooks. For those hesitant or unfamiliar with VBA techniques, Kutools serves as an ideal alternative. With this tool at your disposal, VBA modules, UserForms, Excel 5/95 dialog sheets, and Excel 4 XLM macro worksheets can be removed effortlessly.
Note: To run this feature, you need to trust access to the VBA project object model. In Excel, please navigate to File > Options > Trust Center > Trust Center Settings > Macro Settings, then select the "Trust access to the VBA project object model" option.
Select Kutools > Delete > Batch Remove All Macros.
In the Batch Remove All Macros dialog box that appears, all open workbooks are listed for macro removal. Here's what you need to do next:
To add more workbooks for macro removal, click the Add button, and choose either File or Folder.
If there are any workbooks you wish to exclude from the macro removal process, click the button to remove them.
Once you've listed all the desired workbooks for macro removal in the dialog box, click OK.
A new dialog box appears, indicating how many workbooks Kutools has processed for macro removal. After pressing OK, an automated summary workbook will be generated to outline the results.
Want to access this feature? Download Kutools for Excel now! Alongside this, Kutools offers more than 300 other features. With a 30-day free trial, there's no reason to wait. Try it today!
Activating the Trust access to the VBA project object model option can pose a security risk. It's advised to enable the option only while running the feature. Ensure you deselect the "Trust access to the VBA project object model" option once the feature completes its execution.
Above is all the relevant content related to removing macros in Excel. I hope you find the tutorial helpful. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials.