ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

VBA: Delete all Blank Sheets

Updated on August 31, 2012

Looping Through Each Sheet

Dim S as Worksheet

For Each S in Sheets

	'code to execute on each sheet	
	S.delete

Next S

Determining if a Spreadsheet is Blank

There are two steps in determining if a spreadsheet is blank

1. Determine the Address of the last used Cell

LastCellAddress = S.Cells.SpecialCells((xlCellTypeLastCell)).Address


2. Evaluate the contents of the last used Cells

LastCellContents = S.Cells.SpecialCells((xlCellTypeLastCell)).Text


If both the last cell is $A$1 and it is blank then the worksheet is empty

If LastCellAddress = "$A$1" And LastCellContents = "" Then


A Macro to Delete Each Blank Sheet

Sub deleteblanksheets()

Dim LastCellAddress As String

Dim LastCellContents As String

Dim S As Worksheet

For Each S In Sheets

    LastCellAddress = S.Cells.SpecialCells((xlCellTypeLastCell)).Address
    
    LastCellContents = S.Cells.SpecialCells((xlCellTypeLastCell)).Text
    
    If LastCellAddress = "$A$1" And LastCellContents = "" Then
    
        S.Delete
         
    End If

Next S

End Sub

Annoying Alerts

Disabling Alerts

You will notice when you run your new macro you will get an alert each time before it deletes a sheet. These interrupt execution of your code and are often a pain. These can easily be disabled.

To Disable:

Preface you code with:

Application.DisplayAlerts=False

Be sure to turn alerts back on at the end of your code:

Application.DisplayAlerts=True

Handling Errors

You can't delete all worksheets in a workbook. If all of your sheets are blank VBA will return the above run time error. To produce a more user friendly experience we can introduce an error handler.

This handler will display a more meaningful message to the user, and will alert if a different error is encountered.

On Error GoTo ErrorHandler

ErrorHandler:

'if error 1004 is encountered
If Err.Number = 1004 Then
    'Display more clear error description
    MsgBox ("Delete Failed. All sheets in the workbook are empty one sheet will remain.")
    
    Exit Sub

End If

'if another error occurs
If Err.Number <> 0 Then
    'display error and description
    MsgBox ("Error: " & Err.Number & vbCrLf & " " & Err.Description)

End If

The Finished Product

Sub deleteblanksheets()

On Error GoTo ErrorHandler

Application.DisplayAlerts = False

Dim LastCellAddress As String

Dim LastCellContents As String

Dim S As Worksheet

For Each S In Sheets

    LastCellAddress = S.Cells.SpecialCells((xlCellTypeLastCell)).Address
    
    LastCellContents = S.Cells.SpecialCells((xlCellTypeLastCell)).Text
    
    If LastCellAddress = "$A$1" And LastCellContents = "" Then
    
        S.Delete
         
    End If
    

Next S

ErrorHandler:

If Err.Number = 1004 Then
    
    MsgBox ("Delete Failed. All sheets in the workbook are empty one sheet will remain.")
    
    Exit Sub

End If

If Err.Number <> 0 Then
    
    MsgBox ("Error: " & Err.Number & vbCrLf & " " & Err.Description)

End If
    
Application.DisplayAlerts = True

End Sub

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.