I want a macro which deletes all the sheets which are empty in all the workbooks in the folder
Macro to delete all empty sheets from all the workbooks in the folder
-
-
-
Re: Macro to delete all empty sheets from all the workbooks in the folder
This is not straight forward. You need to define how you determine a sheet is empty within a workbook. For example if you could make a logic decision that is there is no data in column A of a worksheet then you could use something like this to delete all the empty sheets within a workbook.
Code
Display MoreOption Explicit Sub DeleteSheets() Dim ws As Worksheet Dim xRow As Long Application.DisplayAlerts = False For Each ws In Worksheets ws.Select xRow = Cells(Rows.Count, "A").End(xlUp).Row If xRow = 1 And Range("A" & xRow) = vbNullString Then ws.Delete End If Next End Sub
You can then use code to loop through all files in a directory with specific extension depending on your version of excel but I am loath to really give you too much more code without you giving some more details on how you determine a sheet is empty. You need to work out the logic to determine a worksheet is empty.
Remember this - YOU CANNOT UNDO VBA ACTIONS - THERE IS NO UNDO FOR WHAT VBA DOES.
If you want to give me some more information on how to determine a sheet is empty and what sort of files you are going to open and then delete sheets from I will help some more. There is plenty of code out there to show you how to get a collection of files in a directory -
Re: Macro to delete all empty sheets from all the workbooks in the folder
@Smuzon - This is a duplicate thhread. The other post is here http://www.ozgrid.com/forum/showthread.php?t=152652
-
Re: Macro to delete all empty sheets from all the workbooks in the folder
I know some of your request is duplicated in another thread as cytop has indicated.
As I have seen elsewhere in this thread and the other related thread, the definition of what constitutes an "empty" sheet needs clarification. I have assumed that if there are no "shapes" and if there is no data in any of the cells, then the sheet is empty. "Shapes" includes buttons on the worksheet, comments, charts embedded on the sheet, etc as far as I know.
I was working on something close to what you were looking for in relation to another thread. I modified the code and put it into the attached sample file. You should test this on copies of files first, NOT originals and if you plan to run it across a whole folder then use a folder that contains copies of files you want to test. Don't run this on original files until you are satisfied it works!
Whilst I have coded to allow for Excel 2007 and 2010 file types, I do not currently have access to either of these versions to test the code on. The code was written and tested using Excel 2003.
Files that are password protected will be skipped. Files that cannot be opened for whatever reason will be skipped. Files that are "read-only" will be processed but any changes made won't be saved. Of course, a file that is completely "empty" will still contain at least 1 empty sheet after processing.
Note that to declare a FileSystemObject you will need to add Microsoft Scripting Runtime in the VBE. While in the VBE go to Tools-->References and check Microsoft Scripting Runtime.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!