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

Becoming a VBA expert isn't hard
Over 5000 members are improving their VBA skills for free with our email tutorials. Why don't you join them? Our experts share time-saving VBA tips and we'll give you access to our huge macro library - it's sure to speed up your macro development.

I want to get your free VBA material

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 Twitter and check out our VBA Q&A community for more great VBA content.