VBA Delete Blank Subfolders

VBA Delete Empty Subfolders

VBA Delete Empty Subfolders from a specified folder using VBA. First we are checking subfolder has files in it. Files are available we are not deleting that respective subfolder. If files are not available and now subfolder is empty then we are deleting that folder. In the following tutorial we have explained step by step instructions to run example VBA macro code.

VBA Delete Empty Subfolders

Let us see an example macro VBA code to delete an empty or blank Subfolder(s). In the below example we are specifying root folder path. You can change that path as per your requirement.
Main Procedure:

'VBA Delete Blank/Empty Subfolders
Sub VBAF1_Delete_Empty_Subfolders()

     'Variable declaration
    Dim sFldPath As String
        
    'Define Root Folder Path
    sFldPath = "C:\VBAF1"
    
    'Çall SubProcedure
    Call VBAF1_SubProcedure_To_Delete_Empty_Subfolders(sFldPath)
    
End Sub

Sub Procedure(Recursive Method): In the below Sub Procedure we are checking all available subfolders using recursive method. We are checking files count and subfolders count in a folder. If both values are ‘0’ then deleting that folder. Otherwise by following recursive method checking all available subfolders and deleting the blank or empty subfolders.

'VBA Sub Procedure(Recursive Method) to Delete Blank/Empty Sub folders
Sub VBAF1_SubProcedure_To_Delete_Empty_Subfolders(sFolderPath As String)
    
    'Variable declaration
    Dim sFileName As String, oFile As Object, oFolder As Folder
    Dim iFilesCount As Integer, iFoldersCount As Integer
           
    'Check for slash
    If Right(sFolderPath, 1) <> "\" Then sFolderPath = sFolderPath & "\"
        
    'Create FSO Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sFolderPath)
    
    'Check Specified Folder exists or not
    If oFSO.FolderExists(sFolderPath) Then
       'Loop through all subfolders
        For Each oSubFolder In oFolder.SubFolders
            'Çheck available files count in a folder
            iFilesCount = oSubFolder.Files.Count
            iFoldersCount = oSubFolder.SubFolders.Count
            
            'If folder has '0'files then delete subfolder
            If iFilesCount = 0 And iFoldersCount = 0 Then
                'Delete Subfolder
                RmDir oSubFolder
            End If
            
            'Recursive Method
            'Check if any other subfolders within subfolders
            If iFoldersCount <> 0 Then
                Call VBAF1_SubProcedure_To_Delete_Empty_Subfolders(sFolderPath & oSubFolder.Name)
            End If
        Next
    End If
End Sub

Instructions to use VBA Macro Code

Here are the instructions to use above macro in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • Now you can check specified root folder, all the empty or blank sub folders are deleted.

Related Articles

You can also learn complete details like syntax, example and etc by clicking on the following buttons.

VBA File and Directory Functions in Excel Related Posts

Leave a Comment

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