Macro or Program to Delete extra Sheet2 and Sheet3
- Thread starter arunkw
- Start date
Welcome to the forums!
Are the blank sheets really a problem? Having the extra worksheets in the workbooks is causing almost 0 memory growth. You can test this by saving a blank workbook with 3 sheets, and then copying one of those sheets to a new workbook and saving it. The memory used will be almost identical. I'm just wondering if the time to search through all the files, plus the "risk" if anything goes wrong is worth it.
For future note, you can change how many worksheets your workbook starts with by going to Tools - Options - General tab.
Are the blank sheets really a problem? Having the extra worksheets in the workbooks is causing almost 0 memory growth. You can test this by saving a blank workbook with 3 sheets, and then copying one of those sheets to a new workbook and saving it. The memory used will be almost identical. I'm just wondering if the time to search through all the files, plus the "risk" if anything goes wrong is worth it.
For future note, you can change how many worksheets your workbook starts with by going to Tools - Options - General tab.
Hi Luke thanks for the reply, if someone sends me an excel file, with all the content in sheet1 and keeping sheet2 and sheet3 as empty. It is sure you and me would be checking what's in sheet 2 and sheet 3. we will find nothing there that's unproductive. It would be better if we had ran bulk correction program for all the excel files. (There is also a possibility that someone by habit would miss looking at sheet 2 and sheet 3 which might have data ) It's not about the question of memory. It's like having 3 similar wallets and keeping money only in one, and whenever you checkin for paying you have to find out which wallets out of three is empty and from which I have to pay. I am aware of 'Tools - Options - General tab - No. of sheets=1'
The following will check to see if there is any data on Sheet2 and Sheet3, if no data it will delete the sheet.
Sub DeleteEmptySheets()
Application.DisplayAlerts = False
Z = WorksheetFunction.Count(Sheets("Sheet2").Cells)
Z2 = WorksheetFunction.CountA(Sheets("Sheet2").Cells)
if z + z2 = O then
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
End If
Z = WorksheetFunction.Count(Sheets("Sheet3").Cells)
Z2 = WorksheetFunction.CountA(Sheets("Sheet3").Cells)
if z + z2 = O then
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
End If
Application.DisplayAlerts = True
End Sub
Sub DeleteEmptySheets()
Application.DisplayAlerts = False
Z = WorksheetFunction.Count(Sheets("Sheet2").Cells)
Z2 = WorksheetFunction.CountA(Sheets("Sheet2").Cells)
if z + z2 = O then
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
End If
Z = WorksheetFunction.Count(Sheets("Sheet3").Cells)
Z2 = WorksheetFunction.CountA(Sheets("Sheet3").Cells)
if z + z2 = O then
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
End If
Application.DisplayAlerts = True
End Sub
Wow, kchiba the script that you wrote works awesome.
Obscured cell? If data is not in cells be it text, images charts or macro, then it's good to be deleted.
There is one problem, time taken to copy paste the macro in all the worksheet and to delete sheet2 and sheet3 is almost equal to hand pick sheet2 and sheet3 and then delete it.
It would be great to have an External script like Autoit, to scan all my c drive check all the excel files and delete empty sheet2 and sheet3 safely and automatically. How to do that?
Obscured cell? If data is not in cells be it text, images charts or macro, then it's good to be deleted.
There is one problem, time taken to copy paste the macro in all the worksheet and to delete sheet2 and sheet3 is almost equal to hand pick sheet2 and sheet3 and then delete it.
It would be great to have an External script like Autoit, to scan all my c drive check all the excel files and delete empty sheet2 and sheet3 safely and automatically. How to do that?