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.
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
Application.ScreenUpdating = True