As a rule, acquaintance with the VBA language begins with the execution of simple programs called macros. With their help, you can perform functions such as deleting a sheet or row in Excel. In the future, the acquired knowledge can be used to write more complex programs.
What is a macro and why is it needed?
This is a special program written in VBA to perform any functions in the MS Office application. You can use a ready-made solution that is easy to find on the Internet, or write such a program yourself. Even a person who is completely unfamiliar with the VBA language can handle this. It is enough to click the "Record Macro" button, perform some actions, which can then be repeated simply by running this macro, and click the "Stop" button in time.
Delete Excel sheet
This is done as follows:
- Create a new Excel document (using Excel 2010 as an example).
- We insert several sheets and enter arbitrary data into them.
- To open the VBA editor, press Alt + F11.
- To insert a new module (in fact, this is the macro), go to the Insert tab and select Module.
- Insert the code:
Sub DeleteSheet ()
Sheets ("Sheet1"). Delete
End sub
- Close the macro editing window.
- Close the VBA editor.
- We save the book as a document with macro support.
- Open the saved document.
- Go to "View-Macros-Macros" and select DeleteSheet.
- Click "Run." A warning message appears stating that data may exist in the cells of this sheet. To delete an Excel sheet with a macro, click on the "Delete" button.
- To delete a sheet without a warning message, add two lines to the macro code:
Sub DeleteSheet ()
Application.DisplayAlerts = False
Sheets ("Sheet1"). Delete
Application.DisplayAlerts = True
End sub
Delete rows in Excel
Now let's see how to delete rows with Excel macros. First, we remove one line. To make it easier to search for a module, we will call it - DeleteRow.
Sub DeleteRow ()
Worksheets ("Sheet1"). Rows (1) .Delete
End sub
After executing the macro in Excel, the row at the first number will be deleted. The rest will move up. To remove several, change the second line of code to the following:
Worksheets ("Sheet1"). Rows ("2:15"). Delete
In this case, lines two through fifteen will be deleted.
Delete
Macros are programs and can cause significant harm to your computer. Now consider how to remove a macro in Excel.
Open the “View” tab, click on “Macros”, select the top line under the name “Macros”, select the one to be deleted and press the corresponding button.
The process of deleting a macro in Excel 2003 has one feature. To completely remove it, you will need to manually clean all the modules. To do this, go to the Visual Basic Editor.
Removing macros programmatically
The next way you can delete a macro in Excel is to use an add-in - a special program that is installed separately and contains several modules for performing various functions. One such add-on is Kutools. Using this program, you can delete all Excel macros at the same time.
This add-in has over 300 different functions. Note some interesting features:
- conversion of ordinary numbers to Roman and vice versa;
- Currency conversion using current exchange rates;
- unit conversion
- search for cells with specific formatting;
- delete blank sheets, rows, columns.
If you have a large macro base, you can create your own add-in. Thanks to this, it will be possible to distinguish between “friends” and “strangers” (Excel will automatically trust them).
What if it is stored in the personal book? How to delete macro in Excel in this case? First you need to display a hidden window. To do this, select the "View" tab and in the "Window" group, click the "Display" button. Subsequently, the macro deletion procedure includes standard steps.
It is important to note that when deleting through the "View" tab, a macro viewing window opens. By default, “Located in all open books” is selected. How to delete macro in Excel for only one document? You need to select the appropriate item in the “Located” menu: either “This Book” or a menu item with the name of this document.