Count of rows in excels in different Folder RRS feed

  • Question

  • Hi,

    I have 3 folders in C drive


    Under each folder i have 1 excel file with same name abc.excel ( column ID) which has ceratin rows.

    Now how can i calculate number of rows in all file in column ID???

    Any way other than manual?


    Wednesday, January 11, 2017 2:15 PM

All replies

  • Shakky,

    In 3 cells in your excel-file write this 3 formulas:




    It is not exactly what you asked for, but it gives you the count of rows in column ID with something in it.


    Wednesday, January 11, 2017 2:37 PM
  • Hi Shaky1988,

    you had mentioned that you want any other way then manual.

    you can use the code mentioned below.

    Option Explicit
    Sub demo()
    Dim FileSystem As Object
    Dim HostFolder As String
    HostFolder = "C:\Users\v-padee\Desktop\excel files\"
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
    End Sub
    Sub DoFolder(Folder)
        Dim SubFolder
        For Each SubFolder In Folder.SubFolders
            DoFolder SubFolder
        Dim File
        Dim wb As Workbook
        Dim sht As Worksheet
        For Each File In Folder.Files
           Set wb = Workbooks.Open(File)
           Set sht = wb.Worksheets(1)
           Debug.Print (sht.Cells(Rows.Count, "A").End(xlUp).Row & " Rows in Sheet=1 File=" & File)
    End Sub

    I create 1 Folder on Desktop.

    then I create 3 Folders in that Folder.

    each folder contain file with same name.

    now the run the demo sub. output like below.

    I have 1 row in file inside fol-1.

    I have 2 rows in file inside fol-2.

    I have 3 rows in file inside fol-3.

    you can change the folder path according to your requirement.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Thursday, January 12, 2017 12:32 AM