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

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

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:)

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.