How to Batch Delete Multiple Unwanted or Unused Files with Excel VBA

There might be several unwanted and unused files eating your disk space. Cleaning these files might be a huge task. With excel, you can easily find such unused files and delete them instantly to free disk space.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

Remove unwanted sheets from the workbook and rename the Sheet 1 as GUI and save the workbook as macro enabled workbook. As shown in the attached image, create two buttons on the sheet and rename the Button 1 as “Browse” and rename the Button 2 as “Delete Files”. You can either merge multiple columns to create a bigger column to accommodate the file path or you can increase the width of a single column.Prepare The GUI

Let’s make it functional

Copy the script to a new module in your macro enabled workbook. Please ensure that the module name should be Module1. Otherwise, the script will throw an error. Attach the script “Scan_This_Folder” to the button “Browse” and the procedure “Delete_Files” should be attached to the button “Delete Files”.

Sub filefordelete()
    Dim v_var1 As Scripting.FileSystemObject
    Dim v_var2 As Scripting.Folder
    Dim v_var3 As Scripting.File
    scanthis = Range("B3").Text
    Set v_var1 = New Scripting.FileSystemObject
    Set v_var2 = v_var1.GetFolder(scanthis)
    i = 7
    For Each v_var3 In v_var2.Files
        Cells(i, 2) = v_var3
        Cells(i, 11) = v_var3.DateLastModified
        i = i + 1
    Next v_var3
    Set v_var1 = Nothing
End Sub

Sub Delete_Files()
    Dim lr As Long
    Dim r As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    For r = 7 To lr
        Kill Range("B" & r).Value
    Next r
End Sub

Function scanthisfolder() As String
    Dim v1 As FileDialog
    Dim v2 As String
    Set v1 = Application.FileDialog(msoFileDialogFolderPicker)
    With v1
        .Title = "Folder to scan for files"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        v2 = .SelectedItems(1)
    End With
NextCode:
    scanthisfolder = v2
    Set v1 = Nothing
End Function

Sub Scan_This_Folder()
    Range("B3").Value = scanthisfolder()
    Call Module1.filefordelete
End Sub

How does it work?

VBA CodeWhen the user selects a folder, the script reads all files within the selected folder and displays the file name along with the last modified date. Verify the list, remove rows if you want to retain the file and then click the “Delete Files” button. The script will now identify the last used row and delete files reading the file path from the sheet.

Tweak it

As of now, the script scans for files within the selected folder. If there are subfolders within the selected folder, the script cannot scan it. You can tweak the script to scan all subfolders within the root folder and also create a new column to compare the “Last Modified Date” of files with the current date and display the Days. You can then tweak the script to delete files automatically based on the count of days. Though the script uses Microsoft’s file scripting technique, file properties of damaged xlsx files cannot be retrieved.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover word and outlook recovery software products. For more information visit www.datanumen.com.

One response to “How to Batch Delete Multiple Unwanted or Unused Files with Excel VBA”

Leave a Reply

Your email address will not be published. Required fields are marked *