Table of Contents
Using Visual Basic for Applications (VBA) you can create macros that can delete files and folders. To delete folders using VBA, use the FileSystemObject.DeleteFolder method. This requires you to specify the folder path as an argument. You can also delete files using the DeleteFile method. Examples of using these methods in your VBA code are provided to help get you started.
You can use the following methods in VBA to delete folders:
Method 1: Delete All Files in Folder
Sub DeleteFolderContents()
On Error Resume Next
Kill "C:UsersbobbiDesktopMy_Data*.*"
On Error GoTo 0
End Sub
This particular macro will delete all of the files in the folder called My_Data.
Method 2: Delete Entire Folder
Sub DeleteFolder()
On Error Resume Next
'delete all files in folder
Kill "C:UsersbobbiDesktopMy_Data*.*"
'delete empty folder
RmDir "C:UsersbobbiDesktopMy_Data"
On Error GoTo 0
End Sub
This particular macro will delete the entire folder My_Data so that it no longer exists.
The line On Error Resume Next tells VBA that if an error occurs and the folder is not found that no error message should be shown.
We then use On Error GoTo 0 to reset the default error message settings.
If you would like to display an error message if the folder is not found, then simply remove these two lines from the code.
The following examples show how to use each method in practice with the following folder called My_Data that contains three Excel files:
Example 1: Delete All Files in Folder Using VBA
Suppose we would like to use VBA to delete all of the files in the folder called My_Data.
We can create the following macro to do so:
Sub DeleteFolderContents()
On Error Resume Next
Kill "C:UsersbobbiDesktopMy_Data*.*"
On Error GoTo 0
End Sub
Once we run this macro and open the folder again, we will see that all of the files have been deleted:
Example 2: Delete Entire Folder Using VBA
If you would like to use VBA to delete the entire folder called My_Data so that it no longer exists, you can create the following macro:
Sub DeleteFolder()
On Error Resume Next
'delete all files in folder
Kill "C:UsersbobbiDesktopMy_Data*.*"
'delete empty folder
RmDir "C:UsersbobbiDesktopMy_Data"
On Error GoTo 0
End Sub
Once we run this macro and open the File Explorer, we will see that the folder called My_Data no longer exists:
