How to quickly count the number of lines in multiple text files?

Question

I have a more than 100 text files and I have to count the lines for each of them. Column A lists the file name, located in the folder specified in E1. Several files have more than 1 million lines, causing the script to run a terrifyingly long time.

Sub counter()
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim longtext As String
    Dim lines As Variant
    Dim GoToNum As Integer
    Dim Start As Integer
    GoToNum = 2
    Start = 3

    Do Until IsEmpty(Cells(Start, 1))
        GoToNum = GoToNum + 1
        Start = Start + 1
    Loop

    For i = 3 To GoToNum
        If Cells(i, 2).Value <= Cells(2, 5).Value Then
            ConOrg = Cells(1, 4).Value & "\" & Cells(i, 1).Value

            Set ts = fso.OpenTextFile(ConOrg, ForReading, False)
            longtext = ts.ReadAll

            ts.Close
            lines = Split(longtext, vbLf)
            Cells(i, 3) = UBound(lines) - LBound(lines) - 1

        End If
    Next i
End Sub

How can I get the number of the last row (from the text file) to avoid the counting line by line?

Answer 1

How to count the lines within a text file using VBA:

40 MB file (1.7 million lines)
- CountLF = 25.2 seconds
- CountLines = 2.1 seconds

14 B files (6 lines) x 10,000 times
- CountLF = 1.3 seconds
- CountLines = 18.9 seconds

---

Better for Small Files:

Function countLF(fName As String) As Long
    Dim st As String
    Open fName For Input As #1: st = Input(LOF(1), 1): Close #1
    countLF = Len(st) - Len(Replace(st, vbLf, "")) + 1
End Function

Example Usage:

Debug.Print countLF("c:\test.txt")

---

Better for Large Files:

Function countLines(fName As String) As Long
    countLines = CreateObject("Scripting.FileSystemObject").OpenTextFile(fName, 8, True).Line
End Function

Example Usage:

Debug.Print countLines("c:\test.txt")

---

_{More Benchmarking: (2500 tiny text files)
Binary Access/Get (4.32s) Kill=1.17s . . . Open F For Binary Access Read As #1:ReDim...Get #1,,bytes
Line Input/LineInput (4.44s) Kill=1.11s . . . Open F For Input As #iFile...Line Input #1,st
Early Bind/ReuseObj (5.25s) Del=1.12s . . . Set o=New Scripting.FileSystemObject':st=o.OpenTextFile(F).ReadAll()
Early Bind/FreshObj (11.98s) Del=1.35s . . . Set o=New Scripting.FileSystemObject':st=o.OpenTextFile(F).ReadAll()
LateBind/ReuseObj (6.25s) Del=1.47s . . . Set o=CreateObject("Scripting.FileSystemObject")
LateBind/FreshObj (13.59s) Del=2.29s . . . With CreateObject("Scripting.FileSystemObject")}

Answer 2

Give this function a try. It uses the FileSystemObject. Should be faster than read the whole file and split it into single lines. Inspired from Hey, Scripting guy

Function countLines(fName As String) As Long

    Const ForReading = 1
    Dim objFSO  As Object, objTextFile As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTextFile = objFSO.OpenTextFile(fName, ForReading)
    objTextFile.ReadAll
    countLines = objTextFile.Line
End Function