Microsoft Excel - how do i remove extra blank sheets from my excel spreadsheet?

Asked By Nancy on 20-Sep-11 12:25 PM
I would like to know how to remove extra blank sheets from my excel spreadsheets.
Riley K replied to Nancy on 20-Sep-11 12:44 PM
The below Macro will delete empty sheets

Sub DeleteBlankSheets()
  Dim sh As Variant
    
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
On Error Goto Exits:
    
  For Each sh In Sheets
    If Not IsChart(sh) Then
      If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then sh.Delete
    End If
  Next sh
    
Exits:
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub
  
Public Function IsChart(sh) As Boolean
  Dim tmpChart As Chart
  On Error Resume Next
  Set tmpChart = Charts(sh.Name)
  IsChart = IIf(tmpChart Is Nothing, False, True)
End Function
  
 
Regards



Radhika roy replied to Nancy on 20-Sep-11 01:21 PM

Delete Blank (empty), Hidden Rows, Columns, Sheets

These delete actions are simple, but they become tedious and error-prone when you have large amount of data. For example:
  • Delete blank rowscolumnssheets - If a table has thousands of rows, it very hard to find and delete all blank (empty) rows in the middle of the table. It takes a lot of scrolling and still cannot be sure. Same with columns. A long table will make it hard determine if a column is completely empty. With sheets, a large file when tens of sheets makes it tedious to check each one manually.

  • Delete hidden rowscolumnssheets - Excel does not support the deletion of hidden rows in filter results and subtotal. There is also no way to delete hidden columns and sheets unless you unhide them first, one at a time. This is tedious when you have a wide table with many columns or a large file with many sheets. DigDB allows you to delete the hidden rows, columns, sheets in batch.

    Delete blank, empty), hidden

Delete blank (empty) rows

  1. Select a range, invoke 'DigDB->Delete->Rows->Empty', DigDB will treat your selected area as a table to find and delete empty rows in it.

    Or, click a single cell, then invoke the command. This way DigDB assumes the current sheet's entire data area as a single table to find and delete empty rows in it. (A sheet's entire data area means the area formed by the top and bottom non-empty rows and the left-most and right-most non-empty columns in the current sheet).

    Delete blank, empty), hidden 
    Practice file - delete-blank-empty-demo.xls (16k)

  2. All empty rows will be selected to reconfirm before deleting. Click OK to delete.

    Delete blank, empty), hidden

    After empty rows are deleted

    Delete blank, empty), hidden

Delete blank (empty) columns

  1. Select a range, invoke 'DigDB->Delete->Columns->Empty', DigDB will treat your selected area as a table to find and delete empty columns in it.

    Or, click a single cell, then invoke the command. This way DigDB assumes the current sheet's entire data area as a single table to find and delete empty columns in it. (A sheet's entire data area means the area formed by the top and bottom non-empty rows and the left-most and right-most non-empty columns in the current sheet).

    Delete blank, empty), hidden 
    Practice file - delete-blank-empty-demo.xls (16k)

  2. All empty columns will be selected to reconfirm before deleting. Click OK to delete. Note that column headers are ignored in determining if a column is empty.

    Delete blank, empty), hidden

Delete blank (empty) sheets

  1. Invoke 'DigDB->Delete->Sheets->Empty...'

    Delete blank, empty), hidden 
    Practice file - delete-blank-empty-demo.xls (16k)

  2. Empty sheets appear in the to-delete pool. Non-empty sheets appear in the to-keep pool. Click OK to delete. or use the 'Add' and 'Remove' to select what to delete.

    Delete blank, empty), hidden

Delete hidden rows

  1. Click a cell in your table area, invoke 'DigDB->Delete->Rows->Hidden...'. (You can use this delete on AutoFilter, Advanced filter, DigDB filter, table match, Subtotal)

    Delete blank, empty), hidden 
    Practice file - delete-hidden-rows-demo.xls (16k)

    After deletion:

    Delete blank, empty), hidden

Delete hidden columns

  1. Click a cell in your table area, invoke 'DigDB->Delete->Columns->Hidden...'

    Delete blank, empty), hidden 
    Practice file - delete-hidden-columns-demo.xls (16k)

    After deletion:

    Delete blank, empty), hidden

Delete hidden sheets

  1. Invoke 'DigDB->Delete->Sheets->Hidden...'

    Delete blank, empty), hidden 
    Practice file - delete-hidden-sheets-demo.xls (16k)

  2. Hidden sheets appear in the to-delete pool. Visible sheets appear in the to-keep pool. Click OK to delete. or use the 'Add' and 'Remove' to select what to delete.

    Delete blank, empty), hidden

    Click OK to delete hidden sheets.

Pichart Y. replied to Radhika roy on 21-Sep-11 12:23 AM
Hi Radhika roy,

Is this new feature in excel 2010, I've never found it in Version2007 or it is special add-in?

Thanks,

Pichart Y.
Anoop S replied to Nancy on 21-Sep-11 01:00 AM

Delete blank (empty) sheets

  1. Invoke 'DigDB->Delete->Sheets->Empty...'

    Delete blank, empty), hidden

  2. Empty sheets appear in the to-delete pool. Non-empty sheets appear in the to-keep pool. Click OK to delete. or use the 'Add' and 'Remove' to select what to delete.

    Delete blank, empty), hidden