Announcement

Collapse
No announcement yet.

Delete Only Blank Worksheets With No Data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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,

  • #2
    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.
    Code:
    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?
    Boo!

    Comment


    • #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

      Comment


      • #4
        Re: Macro For deleting a work sheet from work book

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

        Comment


        • #5
          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.

          Comment


          • #6


            Re: Macro For deleting a work sheet from work book

            To delete only empty Worksheets use this mod of Nories code
            Code:
            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

            Comment

            Working...
            X