Hello Excellers, welcome back to another #MacroMonday #Excel blog post in my 2020 series. Today, I want to share with you today a handy Excel VBA macro. This macro will delete any leftover black Excel worksheets in your Excel workbook. Usually, I will manually remove any unused or blank worksheets before I send or share an Excel solution. So, this is a really quick way to tidy up my workbook before I send it.
Preparing To Write The Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
Learn More About Your Personal Macro Workbook (PMW).
If you want to read more about your Excel PMW then check out my blog posts below. Creating and Updating Your Personal Macro Workbook Create A Shortcut To Your Personal Excel Macro Workbook Why Is My Personal Macro Workbook Not Loading Automatically?
Starting The Macro. Delete Blank Worksheets.
To start the macro type Sub and then the name of your macro. For this example, I have simply named the macro DeleteBlankSheets.
Once you type Sub Excel will also insert End Sub automatically for you.
The rest of your code will sit in between these lines. [stextbox id=’info’] Sub DeleteBlankSheets()[/stextbox] 
Declaring Variables.
The first piece of code we need to write is a DIM statement. We need to declare two variables. The declaration of any variables. This simply creates a memory container for these values in Excel. I am declaring one variable only in this macro. [stextbox id=’info’] Dim ws As Worksheets[/stextbox]
Error Handling. Using The On Error Resume Next.
The piece of code prevents run time errors. If Excel cannot execute a line of code then a run time error will be displayed. In order to avoid this, we can insert the code below. By using the On Error Resume Next, the code will continue to run. The error will not be fixed, but the code continues to run. This can be a good and bad situation. If you are happy that any errors do not need to be addressed, then go ahead and use the code. [stextbox id=’info’] On Error Resume Next[/stextbox]
Turn Off Screen Updating And Alerts.
The next two lines of code are very useful. They will allow your code to run MUCH faster and stop the screen flicker which sometimes happens as VB code executes. Any screen alerts will not display as we have turned them off. (We turn them back on later in the routine but we will address that later). [stextbox id=’info’] Application.ScreenUpdating = False Application.DisplayAlerts = False[/stextbox]
Looping Through All Worksheets In The Workbook.
For the next part of this Excel macro, we can use the For Each looping method. We simply loop through all of the worksheets in the active workbook and use Application.WorksheetFunction.CountA(ws.UsedRange) = 0. If the used range cells are all empty (COUNTA will capture any cells that are not empty, including those with strings, numbers, special characters etc) then the worksheet is deleted. Excel continues through all of the worksheets until all empty ones have been deleted. [stextbox id=’info’] For Each ws In Application.Worksheets If Application.WorksheetFunction.CountA(ws.UsedRange) = 0 Then ws.Delete End If Next[/stextbox]
Turn On Screen Updating And Alerts.
Now we can turn back on the screen updating and alerts. This reverses the line of code that turned these off before the main part of the code. [stextbox id=’info’] Application.ScreenUpdating = True Application.DisplayAlerts = True[/stextbox]
Ending The Macro.
Finally, once all of the worksheets in the active workbook (that are blank) have been deleted then the code comes to an end. This is done with the End Sub piece of code, this was already inserted into the code when we started writing the macro. [stextbox id=’info’] End Sub[/stextbox]
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series then click on the link below
Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money-back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free. 