PDA

View Full Version : Delete Sheet2 & Sheet3



arunkw
11-22-2010, 06:56 AM
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.

mikerickson
11-28-2010, 03:32 PM
Down load this attachment. Open the file, press the button. Follow the prompts.

The code triggered by the button is: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

arunkw
11-29-2010, 02:37 AM
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.

mikerickson
11-29-2010, 06:56 AM
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)