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 in a text file using VBA:

The fastest method depends on the size of the file(s) you're processing:

	# of lines	filesize	Open statement	FileSystemObject
one GIANT file	1.7 million	40mb × 1	❌ 25.2 sec	✔️ 2.1 sec
many sᴍᴀʟʟ files	6	14b × 10,000	✔️ 1.3 sec	❌ 18.9 sec
			better for sᴍᴀʟʟ files	better for BIG files

---

→ 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 of other tested variations: (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")}