vba code to count rows in multiple .csv files

Litigation Support Tip of the Night is Five Years Old! Five years of tips each night since 2015.

Tonight, I tested out the below vba code posted here by mdmackillop which allows you to generate counts of the number of rows in multiple .csv files.

As always, simply press ALT + F11 to go into Visual Basic and enter this code in a new module for the workbook. List the folder containing your source files on the line beginning: sPath = "

Be sure not to use any other applications on your PC while this macro is running, or it may stop and give you incomplete results.

When it finishes it will generate a list of each source .csv file and show the total row count in an adjacent column.

As always, I confirmed the accuracy of this vba code tonight working with real data.

Sub OpenCSVFiles()

Dim wb As Workbook, wbCSV As Workbook

Dim sPath As String, sFilename As String

Dim NbRows As Long, rg As Range

Set wb = ThisWorkbook

Application.ScreenUpdating = False

sPath = "C:\foofolder5\" '\ added to correct file path

sFilename = Dir(sPath & "*.csv")

Do While Len(sFilename) > 0

Set wbCSV = Workbooks.Open(sPath & sFilename) 'open file

NbRows = wbCSV.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'maximise rows to check; 100 may be exceeded

Set rg = wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'maximise result rows; 100 may be exceeded

rg = sFilename

rg.Offset(0, 1) = NbRows

wbCSV.Close False 'close file

sFilename = Dir

Loop

Application.ScreenUpdating = True

End Sub