Macros provide a great way to automate repetitive tasks. In this way, they help get things done quickly, but there may be times when you want to remove macros from a workbook.
For instance, it’s usually a good idea to delete macros that you no longer need. This makes your macros list smaller. So it gets easier to view the macros that you do need.
Moreover, it keeps your workbook file size small and optimizes speed.
In this tutorial, we will take a look at 3 simple ways to remove macros from an Excel workbook.
Method 1: Remove All Macros by Saving the Workbook in XLSX Format
If you’re an experienced macro user, you might be aware that the only Excel files that can hold macros are those of the XLSM and XLSB formats (or the older XLS format).
As such, if you save your Excel workbook in XLSX or any other format for that matter, your macros will be deleted or lost.
An easy way to remove all the macros from an XLSM or XLSB format workbook is to save it as XLSX and then delete the original if needed (or keep it as a backup if needed).
The great thing about this method is that you can still hold on to the original document containing the macros, in case you think you might need them later.
To save a workbook as XLSX, here are the steps to follow:
- Open the XLSM or XLSB file that contains the macros.
- Click on the File Tab.
- Select Save As from the options on the left sidebar (if you’re on older versions of Excel, select Save As from the File menu).
- Select the folder where you want to save the new file.
- This will open the Save As dialog box. In the field next to ‘File Name’, type the name you want to give the new file.
- In the field next to ‘Save As Type’, click on the dropdown arrow.
- From the dropdown menu that appears, select Excel Workbook.
- Click Save.
- Your workbook will now be saved with a new name and in the XLSX format.
- If you now select the Macro button from the Developer tab, you will find an empty macros list in the Macros dialog box. This means there are no more macros in this new workbook.
- You can now choose to either delete the original file containing the macros or keep it as a backup, in case you need it in the future.
The above steps would remove all the macros from the newly saved file.
This method is not suitable if you want to only remove specific macros and keep the rest. For that, use the methods shown in the next section.
Note: You can also choose to name the new XLSX file with the same name as the original. This will not cause any issues because the extension of the new file will be different from the original.
Also read: How to Remove Add-Ins from Excel?
Method 2: Remove Macros using the Macro Dialog Box
Excel’s Macro dialog box also provides a nice interface to help you accomplish a variety of tasks involving macros.
You can use it to create, run, edit, debug, and delete multiple macros directly from your worksheet window.
To delete one or more macros from your workbook, follow these steps:
- From the View tab, select the Macros button (under the Macros group). Alternatively, you can click on the Developer tab and select the Macros button (under the Code group).
- This will open the Macro dialog box. You will see the list of all macros in the list on the left side of the dialog box. You can delete one, all, or multiple macros from your workbook.
- To delete one macro, just select the macro you want to delete. To delete multiple macros, press down the CTRL key and select the macros you want to delete. To delete all macros, simply select the first macro, press down the SHIFT key and select the last macro name.
- Press the Delete button on the right side of the dialog box.
- When asked if you are sure you want to delete your selected macros, click on the Yes.
- You should find all your selected macros deleted from the list.
Note: If you don’t see all the macros that you want to delete in the Macros dialog box, they are probably saved in some other workbook. Select the dropdown arrow in the field next to ‘Macros in’. Select the All Open Workbooks options. This should display macros in all workbooks that are currently open.
What about Hidden Macros in a Personal Macro Workbook?
If you have macros in a Personal Macro Workbook, you will find that it is not possible to delete them, since they are hidden by default.
In order to delete macros from your Personal Macro Workbook, you need to first unhide them before attempting to delete them.
To unhide and delete macros from your Personal Macro Workbook, follow these steps:
- Select ‘Unhide’ from the View tab, under the Window group.
- This will open the Unhide dialog box. Select your Personal Macro Workbook from the list of hidden workbooks.
- Click OK. This will unhide your Personal Macro Workbook.
- Now you can delete your required macros by following steps 1 to 6 of Method 2.
Also read: How to Clear Formulas in Excel
Method 3: Remove Macros using the Visual Basic Editor
Finally, the third way to remove macros in Excel is through the Visual Basic Editor. This is the area where you do your actual coding and macro development.
To delete macros with the Visual Basic Editor, follow these steps:
- From the Developer tab, select Visual Basic, under the Code group. This will open the Visual Basic Editor. Alternatively, you can directly open it by pressing the ALT+F11 keys on your keyboard.
- In the Visual Basic Editor window, you should see a Project Explorer toolbox containing a list of all your VBA projects, modules, and macros. If you don’t see this toolbox, select Project Explorer from the View menu on top of the Visual Basic Editor window.
- Select the macro that you want to delete, right-click on your selection, and click Remove from the context menu that appears.
- When asked if you want to export the module before removing it, click on No.
- Repeat steps 3 and 4 for any other modules that you want to delete.
- Once all your required modules have been deleted, close the VBA Editor.
In this way, you can delete macros from any workbook.
Also read: Break the External Link in the Worksheet
In this tutorial, we showed you three simple ways to delete one, more than once, or all macros from your work Excel workbooks.
We hope you found them helpful and easy to apply.
Other Excel tutorials you may like:
- Using Application.EnableEvents in VBA in Excel (Explained with Examples)
- How to Reverse a Text String in Excel (Using Formula & VBA)
- Why does Excel Open on Startup (and How to Stop it)
- How to Remove Hyperlinks in Excel
- How to Remove Commas in Excel (from Numbers or Text String)
- How to Save Selection in Excel as PDF
- What is the Excel Personal Workbook Location?
- Unblock Macros in Excel Files Downloaded From the Web
I tried all of the ways you suggested. Excel will not allow me to save as a macro-free workbook. It shows a dialog box with this note:
The following features cannot be saved in marco-free workbooks:
VB Project
Is there to delete the VB project?