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.
Printable View
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.
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]
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.
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)