Excel VBA: Delete Files with Wildcards (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

VBA allows us to delete any file on the computer by utilizing VBA programs. Using simple VBA programs, we may delete any individual file, specified file types, or all files in a specific folder, or even the entire folder. In this tutorial, we’ll show you how to delete files with wildcards with Excel VBA.


4 Effective Ways to Delete Files with Wildcards Using VBA in Excel

In the sections below, we will show you 4 effective ways to delete files with wildcards. The Kill Command is the program that is used to remove any file. To remove any file, first specify the directory of the file, which indicates where the file is placed on the desktop, and then execute the Kill command to remove the file. In the below image, we have a folder named ‘Files’ which will be used to accomplish the task.

Sample Data

1. Delete Excel Worksheet Files with Wildcards Using a VBA Code

First of all, learn how to run VBA codes to delete a file using the kill command. Let’s say you want to delete the file named ‘average-time’ from the given folder. To do so, follow the steps below.

Step 1:

  • Firstly, press  Alt  F11  to open the VBA Macro.
  • Click on the Insert
  • Then, choose the Module

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Step 2:

  • Secondly, click on the marked icon to get the path directory of the file.

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Step 3:

  • Press  Ctrl + to copy the path directory.

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Step 4:

  • Paste the following VBA See carefully, we have entered the file name and extension after the path directory.
Sub Delete_Excel_worksheet_Files_1()
'paste the address and type a wildcard character(*)after a backslash
'Enter the File type .xlsx for excel worksheet
Kill "C:\Users\Admin\Desktop\Files\average-time.xlsx"
End Sub

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Step 5:

  • Save the program and press  F5   to run it.
  • In the below image the result shows that the ‘average-time’ file is deleted.

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Step 6:

  • To delete all the Excel worksheets with the .xlsx extension, paste the following VBA
  • We have used just an asterisk (*) wildcard character after the file pathway.
Sub Delete_Excel_worksheet_Files_1()
'paste the address and type a wildcard character(*)after a backslash
'Enter the File type .xlsx for excel worksheet
Kill "C:\Users\Admin\Desktop\Files\*.xlsx"
End Sub

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Step 7:

  • To run the program, save and press  F5 
  • Therefore, results will show that the Excel worksheets with the .xlsx extension are deleted.

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Read More: How to Use Excel VBA to Move Files


2. Run a VBA Code to Delete Macro Enabled Files in a Folder with Wildcards in Excel

Similar to the previous method, if we want to delete the Excel Macro-Enabled files, we had to add the extension .xlsm. Follow the instructions below to do so.

Step 1:

  • After opening the VBA Macro, paste the following VBA
Sub Delete_Excel_Macro_Files_2()
'Type the file extension .xlsm after the * wildcard character
Kill "C:\Users\Admin\Desktop\Files\*.xlsm"
End Sub

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Step 2:

  • Then, press  F5  to run the program after making sure you saved it.
  • As a result, all the MacroEnabled files are removed.

Effective Ways to Delete Files with Wildcards Using VBA in Excel

 Notes.  Here is an important note that if you want to delete all the excel files both for the different extensions .xlsx and .xlsm you need to just type the extension .xls or .xl as they both match the first part of the extension. Moreover, you can apply it to any other extensions which match with themselves. Follow the steps to see the difference.

  • Simply, paste the following codes with the .xls
Sub Delete_All_Excel_Files_Nt()
'type a common extension .xls after the (*) wildcard character
Kill "C:\Users\Admin\Desktop\Files\*.xls"
End Sub

Effective Ways to Delete Files with Wildcards Using VBA in Excel

  • Therefore, all the excel files are deleted.

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Read More: Excel VBA to Loop Through Files in Folder and Rename


3. Delete an Entire Folder with Wildcards Using Excel VBA

Using basic VBA codes and wildcard characters, you may also delete a whole folder with names. To do so, follow the instructions below.

Step 1:

  • Paste the VBA code with two wildcards characters as shown in the below image.
Sub Delete_All_Files_and_Folder_3()
'Use two (*) wildcard characters to delete all the files in the folder
Kill "C:\Users\Admin\Desktop\Files\*.*"
End Sub

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Step 2:

  • Then, save the program and press  F5  to run it.
  • Therefore, it will remove all files in the ‘Files’

Effective Ways to Delete Files with Wildcards Using VBA in Excel

Step 3:

  • So, to delete the file name too, paste the following VBA code with the RmDir
Sub Delete_All_Files_and_Folder_3()
'Use two (*) wildcard characters to delete all the files in the folder
Kill "C:\Users\Admin\Desktop\Files\*.*"
'This RmDir Command will delete the folder name from the directory
RmDir "C:\Users\Admin\Desktop\Files\"
End Sub

Sample Data

Step 4:

  • Finally, press  F5   to run the program after saving.
  • Therefore, the ‘Files’ folder will be unavailable on the previous path directory.

Sample Data

Read More: How to Use Excel VBA to List Files in Folder


4. Apply the Conditional IF to Delete Files with Wildcards Using Excel VBA

This is a crucial section since you must read it first if you are uncertain about the file type extension. Furthermore, if the requested file does not exist, you must verify it. As a consequence, we’ll use an IF conditional in VBA code to delete files after reviewing the findings. Follow the outlined steps below to have it done.

Step 1:

  • Firstly, paste the following VBA code to delete all excel files (.xls).
Sub IF_Conditional_to_Delete_Files_with_Wildcards_4()
'Declare a variable to define source address
Dim Source_File_Address As String
Source_File_Address = "C:\Users\Admin\Desktop\Files\*.xls"
'Apply IF condition to check whether the file exists
If Dir(Source_File_Address) <> "" Then
    'Type the Kill command to delete files if the file exists
   Kill "C:\Users\Admin\Desktop\Files\*.xls"
Else
    'Type a result to show in a message box if the file does not exist
    MsgBox Source_File_Address & " File does not exist."
End If
End Sub

Sample Data

Step 2:

  • Secondly, save the program.
  • Then, press  F5   to run the program.
  • As a result, you will see all the excel files are deleted as it meets the condition.

Sample Data

Step 3:

  • Furthermore, run the program.
  • This time it will show a message box typed with File does not exist as there is no file for the (.xls) extension as we deleted it earlier.

Sample Data


Conclusion

To summarize, I hope you now know how to delete files in Excel VBA using wildcards. To teach and practice with your data, all of these ways should be employed. Examine the practice book and apply what you’ve learned. We are inspired to continue giving lectures like this because of your crucial support.

Please do not hesitate to contact us if you have any queries. Please share your thoughts in the comments box below.

Stay with us and continue to learn.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo