How to Delete Files Using VBA (With Examples)


You can use the Kill statement in VBA to delete a specific Excel file in a specific folder.

Here is one common way to use this statement in practice:

Sub DeleteFile()

    On Error Resume Next
    Kill "C:\Users\Bob\Desktop\My_Data\soccer_data.xlsx"
    On Error GoTo 0

End Sub

This particular macro deletes the Excel file called soccer_data.xlsx located in the following folder:

C:\Users\Bob\Desktop\My_Data

The line On Error Resume Next tells VBA that if an error occurs and the file 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 file is not found, then simply remove these two lines from the code.

The following example shows how to use this syntax in practice.

Example: Delete File Using VBA

Suppose we have the following folder with three Excel files:

Suppose we would like to use VBA to delete the file called soccer_data.xlsx.

We can create the following macro to do so:

Sub DeleteFile()

    On Error Resume Next
    Kill "C:\Users\Bob\Desktop\My_Data\soccer_data.xlsx"
    On Error GoTo 0

End Sub

Once we run this macro and open the folder again, we will see that the file called soccer_data.xlsx has been deleted:

All other files remained untouched in the folder.

If you would like an error message to be shown if the file doesn’t exist, you can use the following macro instead:

Sub DeleteFile()

    Kill "C:\Users\Bob\Desktop\My_Data\soccer_data.xlsx"

End Sub

When we run this macro, we receive the following error message:

We receive this error message because the file soccer_data.xlsx has already been deleted and no longer exists in the folder.

Note: Be aware that the Kill statement permanently deletes a file and does not simply send it to the recycling bin.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Number of Sheets in Workbook
VBA: How to Extract Data from Another Workbook
VBA: How to Delete Sheet if Name Contains Specific Text

Featured Posts

Leave a Reply

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