Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Delete Only Blank Worksheets With No Data

  1. #1
    Join Date
    6th September 2006
    Posts
    3

    Delete Only Blank Worksheets With No Data

    Hello everyone,

    I have a problem here.I have a excel work book which has more than 100 work sheets in it. First i will run a report in Business objects and then save the report as excel work book. Now here is my question.Is there any Macro which searches for blank work sheet from the 100 work sheets and delete it automatically.

    Thanks in advance,

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,541

    Re: Macro For deleting a work sheet from work book

    It depends what you mean by a blank worksheet, and how you would determine if the worksheet is blank.

    For example the following will delete every worksheet that doesn't have a value in A1.
    VB:
    Dim ws As Worksheet 
    For Each ws In Worksheets 
        If ws.Range("A1").Value = "" Then 
            Application.DisplayAlerts = False 
            ws.Delete 
            Application.DisplayAlerts = True 
        End If 
    Next ws 
    
    
    By the way would it not be better to eliminate the problem at source?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    6th September 2006
    Posts
    3

    Re: Macro For deleting a work sheet from work book

    Thanks Norie,

    For example i have 100 work sheets(TABS) in a single work book. In that the 57th work sheet (TAB) does not have any data in it I mean its completely empty then what would be the code to delete only that 57th worksheet.

    Thanks in advance

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,541

    Re: Macro For deleting a work sheet from work book

    But how would you be identifying that sheet as being blank?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    6th September 2006
    Posts
    3

    Re: Macro For deleting a work sheet from work book

    The code which you gave is working fine. And For some tabs the B10 cell is empty and for some tabs the B15 cell is empty can we include it in code so that it would check both the cell and then delete the spread sheet.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    Re: Macro For deleting a work sheet from work book

    To delete only empty Worksheets use this mod of Nories code
    VB:
    Sub DeleteBlankWs() 
         
        Dim ws As Worksheet 
        For Each ws In Worksheets 
            If WorksheetFunction.CountA(ws.Cells) = 0 Then 
                Application.DisplayAlerts = False 
                ws.Delete 
                Application.DisplayAlerts = True 
            End If 
        Next ws 
    End Sub 
    
    

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Delete Blank Rows Above Data Table
    By badger55 in forum EXCEL HELP
    Replies: 2
    Last Post: March 29th, 2008, 01:10
  2. Delete Worksheets With Only One Row Of Data
    By Excel Chick in forum EXCEL HELP
    Replies: 4
    Last Post: February 11th, 2008, 02:00
  3. Delete Rows If Data Exists On Other Worksheets
    By pukks in forum EXCEL HELP
    Replies: 2
    Last Post: February 1st, 2008, 21:15
  4. Delete row on blank
    By markc in forum EXCEL HELP
    Replies: 1
    Last Post: July 17th, 2006, 16:59
  5. blank row delete?
    By billgras in forum EXCEL HELP
    Replies: 1
    Last Post: June 2nd, 2005, 21:54

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno