none
Count of rows in excels in different Folder RRS feed

  • Question

  • Hi,

    I have 3 folders in C drive

    a,b,c

    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?


    Shakky

    Wednesday, January 11, 2017 2:15 PM

All replies

  • Shakky,

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

    =COUNTA('C:\a\[abc.xlsm]Sheet1'!$ID:$ID)

    =COUNTA('C:\b\[abc.xlsm]Sheet1'!$ID:$ID)

    =COUNTA('C:\c\[abc.xlsm]Sheet1'!$ID:$ID)

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

    Jan

    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
        Next
        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)
            wb.Close
        Next
    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.

    Regards

    Deepak


    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 MSDNFSF@microsoft.com.

    Thursday, January 12, 2017 12:32 AM
    Moderator