Counting number of rows in Workbooks in folders and sub folders.

[Alexandra_Sergejeva]

Hi everyone! I'm trying to count number of rows into files from some folders and sub folders. Folders and sub folders paths (C:\Users\Desktop\vba, ... etc.) are written in a G column. The result must be inserted in a column F. Maybe some one could explain, please, what is wrong with this loop, because number of rows in each file in sub folder is indicated in a wrong position in column F.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;"></code>[COLOR=#00008B]Sub[/COLOR] CountRows() 
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">[COLOR=#00008B]Dim[/COLOR] wbSource [COLOR=#00008B]As[/COLOR] Workbook, wbDest [COLOR=#00008B]As[/COLOR] Workbook    
[COLOR=#00008B]Dim[/COLOR] wsSource [COLOR=#00008B]As[/COLOR] Worksheet, wsDest [COLOR=#00008B]As[/COLOR] Worksheet    
[COLOR=#00008B]Dim[/COLOR] strFolder [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]String[/COLOR], strFile [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]String[/COLOR]    
[COLOR=#00008B]Dim[/COLOR] lngNextRow [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]Long[/COLOR], lngRowCount [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]Long[/COLOR]    
[COLOR=#00008B]Dim[/COLOR] LastRow    
[COLOR=#00008B]Dim[/COLOR] cl [COLOR=#00008B]As[/COLOR] Range    
Application.ScreenUpdating = [COLOR=#800000]False[/COLOR]    
[COLOR=#00008B]Set[/COLOR] wbDest = ActiveWorkbook    
[COLOR=#00008B]Set[/COLOR] wsDest = wbDest.ActiveSheet    
LastRow = wsDest.Cells.Find([COLOR=#800000]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    
[COLOR=#00008B]For[/COLOR] [COLOR=#00008B]Each[/COLOR] cl [COLOR=#00008B]In[/COLOR] wsDest.Range([COLOR=#800000]"G11:G"[/COLOR] & LastRow)        
strFolder = cl.Value        
strFile = Dir(strFolder & [COLOR=#800000]"/"[/COLOR])        
lngNextRow = [COLOR=#800000]11[/COLOR]        
[COLOR=#00008B]
Do[/COLOR] [COLOR=#00008B]While[/COLOR] Len(strFile) > [COLOR=#800000]0[/COLOR]            
[COLOR=#00008B]Set[/COLOR] wbSource = Workbooks.Open(Filename:=strFolder & [COLOR=#800000]"/"[/COLOR] & strFile)            
[COLOR=#00008B]Set[/COLOR] wsSource = wbSource.Worksheets([COLOR=#800000]1[/COLOR])            
lngRowCount = wsSource.UsedRange.Rows.Count            
wsDest.Cells(lngNextRow, [COLOR=#800000]"F"[/COLOR]).Value = lngRowCount - [COLOR=#800000]1[/COLOR]            
wbSource.Close savechanges:=[COLOR=#800000]False[/COLOR]            
lngNextRow = lngNextRow + [COLOR=#800000]1[/COLOR]            
strFile = Dir        
[COLOR=#00008B]Loop[/COLOR]    
[COLOR=#00008B]Next[/COLOR] cl 
...
End Sub</code>