Consulting

Results 1 to 2 of 2

Thread: delete an empty sheets

  1. #1
    VBAX Mentor
    Joined
    Jun 2005
    Posts
    374
    Location

    delete an empty sheets

    hello
    the following macro delete empty sheets .is it possible to get a count of how many sheets where deleted,and maybe a list of there names on a msgbox.
    [VBA]
    Sub DelWS()
    Application.DisplayAlerts = False
    Dim Sh As Worksheet
    For Each Sh In ActiveWorkbook.Worksheets
    If Application.WorksheetFunction.CountA(Sh.Cells) = 0 Then
    If Worksheets.Count > 1 Then
    Sh.Delete
    End If
    End If
    Next
    Application.DisplayAlerts = True
    End Sub

    [/VBA]
    thanks
    moshe

  2. #2
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    [VBA]
    Sub
    DelWS()
    Application.DisplayAlerts = False
    Dim Sh As Worksheet ,counter as integer,shtString as String
    Counter = 0
    shtString = ""
    For Each Sh In ActiveWorkbook.Worksheets
    If Application.WorksheetFunction.CountA(Sh.Cells) = 0 Then
    If Worksheets.Count > 1 Then
    counter = counter + 1
    shtString = shtString & "," sh.Name
    Sh.Delete
    End If
    End If
    Next
    Application.DisplayAlerts = True
    MsgBox Counter & " Sheets Deleted" & vbNewLine & _
    "Names: " & shtString
    End Sub
    [/VBA]

Posting Permissions

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