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 Left(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 Demo()
Call CountFilesInFolder("C:\Users\Ryan\Documents\", "*.xls*")
End Sub

Count All Files in Folder

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

Only Count Files with "report" in the Filename

Sub Demo()
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!

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too


Your time is valuable. It's time to become a VBA expert.

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.