Consulting

Results 1 to 4 of 4

Thread: Delete Sheet2 & Sheet3

  1. #1
    VBAX Newbie
    Joined
    Nov 2010
    Posts
    2
    Location

    Delete Sheet2 & Sheet3

    I have lot of old excel sheet which has sheet2 & sheet3 as blank/empty/unused.
    I am searching for a VBA which could delete these sheets safely once they are verified that they don't have any data. Being non-programmer I am not able to write VBA.

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Down load this attachment. Open the file, press the button. Follow the prompts.

    The code triggered by the button is:[VBA]Sub DeleteBlankSheets()
    Dim PathToFile As String
    Dim workingWorkbook As Workbook
    Dim sheetIndex As Long, i As Long, aSheetFlag As Boolean

    PathToFile = Application.GetOpenFilename
    Do Until PathToFile = "False"
    On Error Resume Next
    Set workingWorkbook = Nothing
    Set workingWorkbook = Workbooks.Open(PathToFile)
    Set workingWorkbook = Workbooks(Trim(Right(Replace(PathToFile, Application.PathSeparator, String(255, " ")), 255)))
    On Error GoTo 0
    With workingWorkbook
    aSheetFlag = False
    For i = .Worksheets.Count To 2 Step -1
    With .Worksheets(i)
    If Application.CountA(.Cells) = 0 Then
    If MsgBox("Delete worksheet " & .Name & " of workbook " & .Parent.Name & "?", vbYesNo) = vbYes Then
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
    Else
    aSheetFlag = True
    End If
    Else
    aSheetFlag = True
    End If
    End With
    Next i
    With .Worksheets(1)
    If aSheetFlag And (Application.CountA(.Cells) = 0) Then
    If MsgBox("Delete worksheet " & .Name & " of workbook " & .Parent.Name & "?", vbYesNo) = vbYes Then
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
    End If
    End If
    End With
    .Close SaveChanges:=True
    End With
    PathToFile = Application.GetOpenFilename
    Loop
    End Sub[/VBA]

  3. #3
    VBAX Newbie
    Joined
    Nov 2010
    Posts
    2
    Location

    Smile Thanks a million

    Thanks a million mikerickson, I you code is working, can you know How can I use it in Autoit script to achieve same functionality not through the VBA but through the Autoit script.

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    I don't know what an AutoIt script is. It could be made into an AppleScript.

    (Long term the fix is to set Excel>Preferences>General Sheets In New Workbook = 1)

Posting Permissions

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