How to count number of rows in workbooks from sub folders in VBA?

Question

I'm trying to count number of rows into files from some folders and sub folders. Folders and sub folders paths are written in a G column.

Sub CountRows()
    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim strFolder As String, strFile As String
    Dim lngNextRow As Long, lngRowCount As Long
    Dim LastRow
    Dim cl As Range

    Application.ScreenUpdating = False

    Set wbDest = ActiveWorkbook

    Set wsDest = wbDest.ActiveSheet

    LastRow = wsDest.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For Each cl In wsDest.Range("G11:G" & LastRow)
        strFolder = cl.Value
        strFile = Dir(strFolder & "/")
        lngNextRow = 11

        Do While Len(strFile) > 0
            Set wbSource = Workbooks.Open(Filename:=strFolder & "/" & strFile)
            Set wsSource = wbSource.Worksheets(1)
            lngRowCount = wsSource.UsedRange.Rows.Count

            wsDest.Cells(lngNextRow, "F").Value = lngRowCount - 1
            wbSource.Close savechanges:=False
            lngNextRow = lngNextRow + 1

            strFile = Dir

        Loop
    Next cl
...

The result must be inserted in a column F. Maybe some one could explain, please, what is wrong with this loop, because count of rows in each file repeats in column F by number of directories.

For example, if I have 1 folder with 3 files, in column G I will have 3 directories ( 1 directory for each from 3 files C:\Users\Desktop\vba_files\ etc.), then I will get 3 numbers in column F that are a count of rows in each workbook from folder C:\Users\Desktop\vba_files\, but each count repeated 3 times (by number of directories).

It looks in a following way:

enter image description here

yes, sorry, i missed some lines in my example, i've just updated it — Ale
What is the use of LastRow? Also, I would replace lngNextRow = 11 by lngNextRow = wsDest.Range("F" & wsDest.Rows.Count).End(xlup).Row + 1 — simpLE MAn
LastRow is necessary, becouse the count of directories in column G is unknown, so it goes from G11 till the LastRow. — Ale
You could add a simple check at the start of the for each if the cell before has the same value than the current(If cl.Value <> strFolder Then) — Siphor

aucuparia aucuparia · Accepted Answer · 2014-05-07T15:53:10

There is a problem with the logic in the code. You start off with column G containing an entry for every file, so for a folder with 3 files there are 3 repeated rows. But the Do While loop returns a length for every file in the current directory (since you keep calling Dir with no arguments). Then the For Each loop moves on to the next cell in column G, which contains the same directory again, the Do While loop starts again, and you get another 3 entries in column F.

I'm not sure why you would want to start with column G having those repeats, but if you do then you only need a single loop that runs down each cell in column G, and checks whether the directory has changed each time. The code below does this:

Sub CountRows()
    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim strFolder As String, strFile As String
    Dim lngNextRow As Long, lngRowCount As Long
    Dim LastRow
    Dim cl As Range

    'Application.ScreenUpdating = False

    Set wbDest = ActiveWorkbook
    Set wsDest = wbDest.ActiveSheet

    LastRow = wsDest.Cells(wsDest.rows.count,7).end(xlUp).Row
    lngNextRow = 11
    strFolder = ""

    For Each cl In wsDest.Range("G11:G" & LastRow)
            If cl.Value <> strFolder Then
                    strFolder = cl.Value
                    strFile = Dir(strFolder & "/*.xl*")
            Else
                    strFile = Dir
            End If

            If Len(strFile) > 0 Then
                    Set wbSource = Workbooks.Open(Filename:=strFolder & "/" & strFile)
                    Set wsSource = wbSource.Worksheets(1)
                    lngRowCount = wsSource.UsedRange.Rows.Count

                    wsDest.Cells(lngNextRow, "F").Value = lngRowCount - 1
                    wbSource.Close savechanges:=False
                    lngNextRow = lngNextRow + 1
            End If
    Next cl
End Sub

Note that if the number of files in the directory is greater than the number of corresponding entries in column G then the extra files will be ignored.

I added a filter to the first call to Dir() to pick up only Excel files. I also changed the way LastRow is calculated to a slightly safer way (your code would give wrong results if there was any data below and to the right of the last cell in G).

A neater way of achieving something like this is probably:

have the input just a list of directories with no duplication
loop through the input
for each input cell, output all files and counts on a separate output sheet

then you can pick up however many files are in each folder without having to get the number of entries in column G right.

How to count number of rows in workbooks from sub folders in VBA?

1 Answers