There are times when we have to create or add sheet and later on we found of no use of that sheet, hence we get need to delete sheet quickly from the workbook. This article focus on saving time & provide code for removing sheets without any prompt message box using vba macro / code. It could be one sheet or several. And excel gives a popup alert to confirm the sheet deletion. If its one sheet, its manageable. But if you have to delete several sheets, you will get one popup per sheet.
You often create multiple sheets using VBA. But in the end, you don’t need them actually. You would want to delete them after your work is done. It will keep your file lighter, faster and sensible.
VBA code to delete a sheet is simple
Sheets("SheetName").Delete
Let's say you have this code to add a sheet and then delete it when work is done.
Sub AddAndDeleteSheet() Sheets.Add 'Adds a new sheet to the active workbook '---- '---- '---- 'some work on sheet done ActiveSheet.Delete 'deletes the active Activesheet End Sub
When you execute this code, Sheets.Add will work without any prompt but when compiler will come to ActiveSheet.Delete it will prompt a message like this.
Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.
To do this we will switch off the display alert button of Excel Application.
The code below will bypass this alert message and the sheet will be deleted without any intimation.
Sub AddAndDeleteSheet() Application.DisplayAlerts = False 'switching off the alert button Sheets.Add '---- '---- '---- 'some work on sheet done ActiveSheet.Delete Application.DisplayAlerts = True 'switching on the alert button End Sub
Here we are deleting Activesheet using VBA. You can delete any sheet using VBA. Just write Sheets("Sheetname").delete. Moving on...
DisplayAlerts is a property of Application object in VBA. Here we are switching it off at the beginning of our code and switching it on at the end of the code. The above code will confirm deletion without any intimation.
Note:The above code will ignore all the warnings thrown by excel. If you want to allow only sheet deletion than use this line.
Application.DisplayAlerts = False 'switching off the alert button ActiveSheet.Delete Application.DisplayAlerts = True 'switching on the alert button
This will ignore only sheet deletion warning.
If you have multiple sheets to delete and are using a loop, try this code –
Option Explicit Sub macro2() Dim i As Long Application.DisplayAlerts = False For i = 1 to Worksheets.Count If Worksheets(i).Name Like "Test*" Then Worksheets(i).Delete Next i Application.DisplayAlerts = True End Sub
That’s it. It’s done. It easy like switching your fan on and off. Isn’t it?
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.comDownload file
Delete sheets without confirmation prompts using VBA in Microsoft Excel
Related Articles:
Split Excel Sheet Into Multiple Files Based On Column Using VBA
Change The Default Printer Using VBA in Microsoft Excel 2016
Turn Off Warning Messages Using VBA in Microsoft Excel 2016
Display A Message On The Excel VBA Status Bar
Insert Pictures Using VBA in Microsoft Excel 2016
How To Loop Through Sheets In Excel Using VBA
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity
How to use the VLOOKUP Function in Excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Are you sure that your code to delete multiple sheets work?
For me it works by starting at the end (last sheet)
Sub delWorksheet()
' delete all Worksheet without "Something"
Dim n As Long
Application.DisplayAlerts = False
For n = Worksheets.Count To 1 Step -1
If Worksheets(n).Name Like "Something" Then
'do nothing
Else
Worksheets(n).Delete
End If
Next n
Application.DisplayAlerts = True
End Sub
Thanks! Great help!
I tried to click on the Excel Forum link, but my company laptop has the site blocked. It says because it may pose a security threat? Any other way I can access? Thanks again
Hi Tracy,
Your company might has set some sort of firewall and because of that you might not be able to access the site. Could you try using some other internet connection may be at home or so.
Do let us know if it works then.
Best regards,
Team Excel Tip & Excel Forum
Hi, I told you I would be back. Here is my new situation. I need to move all the data from the multiple tabs onto a new worksheet. All of the worksheets have the same headers and # of columns of data, so I would need on the new worksheet to have them all line up underneath each other so no rows are skipped. On the new worksheet, I would need column A to be titled "Facility #" and the facility # is on each tab name. For example, the tabs are named the actual facility #, which is anywhere between 3 - 5 digits, _2015 month #. (IE - 79810_201506). Thanks again for your assistance 🙂
Hi Tracy,
Thanks once again for writing us.
We would request you to login on Excel Forum for your any difficulty or complicated MS-Excel/VBA query. Our experts will surely assist you.
Keep visiting us!
Thanks,
Team Excel Tip & Excel Forum
I got it! There were 2 spaces behind the 2015, so I changed it to 7 and added to 2 spaces. Thanks again for your assistance. Will have another situation soon. MANY THANKS! 🙂
Thanks for your quick response. I tried that, but nothing happened. I saved the macro and rebooted hoping that would make a difference. Any other suggestions? Thanks again 🙂
Hi Tracy,
Please check the following code:
Sub DeleteSheets()Dim Counter As Integer
Application.DisplayAlerts = False
For Counter = ThisWorkbook.Worksheets.Count To 1 Step -1
If Right(Worksheets(Counter).Name, 5) = "_2015" Then Sheets(Counter).Delete
Next Counter
Application.DisplayAlerts = True
End Sub
Regards,
Ashish
I am trying to delete multiple sheets with the ending _2015. I would like to delete them all at once. The problem is there are about 120 sheets and they either have the ending ###_2015 and others are the ###_2015## (these are the ones I want to keep). I am either deleting everything or the warning appears and I delete them one at a time. Thanks in advance 🙂
Thank you for the reply..it was very helpful
many thanks for sharing this
I want to learn in depth that how VBA delete excel sheet without prompt as I am not good in VBA.
Thanx
Hi Teressa,
Greetings from Excel Tip & Excel Forum!
Thanks for visiting us and If you really want to learn VBA in depth, keep visiting us regularly as we at Excel Tip, try to bring new stuff every day on MS-Excel & VBA.
In case you have any complicated or simply query, you can visit us at Excel Forum and can ask to our experts.
Thanks,
Team Excel Tip & Excel Forum
"If anyone uses ASAP utilities (personally I can't live without it) there is a list all range names function that lists all range names on a new worksheet.
Even after deleting all range names in INSERT/DEFINE/DELETE menu it still lists them all. Is this a problem with ASAP or are they still really there?"