Use this VBA macro to count the files in a folder. This macro can return the number of total files in a folder or the number of files of a certain type. Because it’s based on my VBA loop through files in a folder macro, it accepts the same two arguments, one of which is optional:

  1. The directory you want to count the number of files in
  2. An optional filter for things like a file extension. This argument accepts an * as a wild card!

This macro works really fast and by default reports the total number of files in a msgbox. You can change it to send the total number of files to a cell if you desire.


Macro to Count Files in a Folder

Private Sub CountFilesInFolder(strDir As String, Optional strType As String)
'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: This macro counts the files in a folder and retuns the result in a msgbox
'INPUT: Pass the procedure a string with your directory path and an optional
'       file extension with the * wildcard
'EXAMPLES: Call CountFilesInFolder("C:\Users\Ryan\")
'          Call CountFilesInFolder("C:\Users\Ryan\", "*txt")
    Dim file As Variant, i As Integer
    If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
    file = Dir(strDir & strType)
    While (file <> "")
        i = i + 1
        file = Dir
    Wend
    MsgBox i
End Sub

How to use the CountFilesInFolder Macro

To help get you started, here are a few examples of how to use the CountFilesInFolder macro:

Count txt files in Folder

Sub Demo()
Call CountFilesInFolder("C:\Users\Ryan\Documents\", "*txt")
End Sub

Count Excel files in Folder

Sub Demo2()
Call CountFilesInFolder("C:\Users\Ryan\Documents\", "*.xls*")
End Sub

Count All Files in Folder

Sub Demo3()
Call CountFilesInFolder("C:\Users\Ryan\Documents\")
End Sub

Only Count Files with "report" in the Filename

Sub Demo4()
Call CountFilesInFolder("C:\Users\Ryan\Documents\", "*report*")
End Sub

I think this macro is a great way to start off the month of August! I hope you you agree:)

Submit a comment below if you like this macro or have more questions. As always, subscribe to my email list, share this article on social media and follow me on Google+ and Twitter for more great VBA content!


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.