Deletion Macro

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Is it possible to have a macro, which could delete the contents in numerous workbooks?

Activated by an IF statement in a seperate workbook

=IF(A1=B1,"Execute","Do Not Execute")

A1 & B1 being dates (A1 '=today()')
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
The simple answer is yes. You would need to be a bit more specific on what you want eg

every workbook in a folder or only specific workbooks? You would need to open each workbook, delete the contents then save and close. So if there are a lot of workbooks it could take some time.

delete contents in all sheets, a specific range ?

When is the IF checked? On opening the workbook, when a cell is changed?
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Excellent, thankyou

I need to look at this further

Could i ask excel to look at all files in a directory, identify .xls files & delete every single worksheet in all .xls files found, leaving the file names in tact?

I ask this question due to the fact that we create new monthly documents, in a specific DIR each month, the file names are generic & i'm after a bulk way to delete these worksheets, leaving the file names in tact for their replacement

Will XP's registry have a record of the deletion?

Any help with code greatly appreciated

Mark
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
Yes, but a workbook must contain at least one sheet. There would be no record of the deletion, but there will be a record somewhere of who last modified the file. Example below. You could re-name the remaining sheet to "Sheet 1" or whatever you want as well. This macro requires a reference to the Microsoft Scripting runtime (Tools->References in the VBE)

Code:
Sub SubDeleteAllSheets()
'macro to delete sheets of all workbooks in specified directory
'requires reference set to Microsoft Scripting runtime
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim mySheet As Worksheet

        
    'set path for files - change for your folder
    Const myDir As String = "C:\Temp\"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file. check if Excel and if so loop through all sheets and delete (except for one)
    For Each objFile In myFolder.Files
        If Right(objFile.Name, 3) = "xls" Then
            Workbooks.Open myDir & objFile.Name
            'loop through and delete each sheet
            Application.DisplayAlerts = False
            For Each mySheet In Workbooks(objFile.Name).Sheets
                'cannot delete last sheet therefore check for error
                On Error Resume Next
                mySheet.Delete
                On Error GoTo 0
            Next mySheet
            'clear all cells in remaining sheet
            ActiveWorkbook.Sheets(1).Cells.Clear
            Workbooks(objFile.Name).Close savechanges:=True
            
            Application.DisplayAlerts = True
        End If
    Next objFile
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963

ADVERTISEMENT

This macro requires a reference to the Microsoft Scripting runtime (Tools->References in the VBE)

Whats the above? :confused:

Thanks for the code BTW :)
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
The code refers to the FileSystemObject. This is an object not found in normal VBA and is part of something called the Microsoft Scripting runtime. In order to use the object you have to set a reference to it and this is done through the Tools->Reference on the VBE. Just click on the correct check box and the reference will be set.
 

Forum statistics

Threads
1,136,427
Messages
5,675,777
Members
419,585
Latest member
popsin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top