How to Delete Worksheets Using VBA

Save

In Microsoft Excel, you can remove a worksheet by right-clicking on its tab and selecting "Delete" from the context menu. A small window will prompt you to confirm your action. This helps prevent any accidental deletions. Going through the confirmation prompt can be troublesome, however, if you are trying to remove many sheets. Fortunately, you also can delete worksheets using VBA. Excel VBA comes with a "Sheets" function that you can use to create, rename, move and delete worksheets. With a few more lines of code, you can bypass the confirmation window and remove worksheets faster.

  • Start Microsoft Excel. Press "Ctrl" plus "O," select your workbook from the list and click "Open."

  • Press "Alt" plug "F8" to open the "Macros" window. Type "RemoveSheets" in the "Macro name" box and click "Create." This opens the Visual Basic Editor (VBE).

  • Enter the following code in the VBE.

    Sub RemoveSheets()

    wSheets = Array("SHEET1", "SHEET2", "SHEET3")

    Application.DisplayAlerts = False

    For n = 0 To UBound(wSheets)

    On Error Resume Next

    Sheets(wSheets(n)).Delete

    Next n

    Application.DisplayAlerts = True

    End Sub

  • Replace "Sheet1," "Sheet2" and "Sheet3" with the names of the worksheets that you'd like to delete. To add another name to the list, type a comma and insert the name of the sheet before the closing parenthesis of the Array function. Be sure to enclose it in quotes.

  • Press "Alt" plus "Q" to close the VBE.

  • Press "Alt" plus "F8" to open the "Macros" window again. Select "RemoveSheets" from the list, then click "Run."

Tips & Warnings

  • To quickly run the macro, you may bind a keyboard shortcut to it. Press "Alt" plus "F8" to open the "Macros" window, select "RemoveSheets" and click "Options." Click the empty box below "Shortcut Key" and type your preferred letter shortcut.
  • To keep your macro, save your file as a macro-enabled workbook. Press "F12," type your preferred file name and choose "Excel Macro-Enabled Workbook" from the drop-down menu beside "Save as Type." Click "Save."
  • Macros are disabled by default in Microsoft Excel. To enable them, click the Microsoft Office button and select "Excel Options." Under "Popular," make sure that "Show Developer Tab in the Ribbon" is checked. Go to "Trust Center" and click "Trust Center Settings." Go to "Macro Settings" and select "Enable All Macros." Click "OK" to save your changes and restart Microsoft Excel.

References

Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!