I'm working on a report of quality control. I've got a table, with all the points of control inside, and each time, when I get a new test result, Excel should fill this table. My code already works, but slowly (I have 15000 lines in this table, and it takes me 4 minutes to fill it), so I wish I can accelerate a little bit.

Here is my general idea:

The results of one test is composted by nearly 3000 .txt files, and I have a list L which contains the names of all these files that I should open and import the data into Excel. I open the list L, and open the .txt file one by one, to import the results into a sheet "Brouillon" all at once, then fill the table with its help. I think it could be faster.

    'to chose the list L
    Private Sub Button_Parcourir_Click()

    With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .InitialFileName = "import_excel.txt"
    .Show
    listPath = .SelectedItems(1)
    End With
    TextBox1.Text = listPath
    End Sub

<!--- --->

    Private Sub Button_Importer_Click()

    'create a sheet named "Brouillon":
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "Brouillon"
    Range("A1").Select

    'open the list L and import the data into "Brouillon":
    list_de_Controle = "TEXT;" & listPath
    Open listPath For Input As #1
    Do While Not EOF(1)
        Line Input #1, nom_de_Fich
        mfile = Dir(nom_de_Fich & "*.*")

        If mfile <> "" Then
            Open nom_de_Fich For Input As #2
            Fich_dansList = Fich_dansList & nom_de_Fich & "|"
            Inserer_contenu
            Close #2
        End If
    Loop
    Close #1

    'count how many lines and columns we have in "Data":
    Worksheets(Array(1)).Select
    Range("A3").Select
    ActiveCell.End(xlDown).Select
    ActiveCell.End(xlToRight).Select
    ligne_Data = Selection.Row
    ma_Colonne = Selection.Column + 1

    'count how many lines and columns we have in "Brouillon":
    Count_Brouillon
    'put a flag to reduce the loop
    marque_ligneBrouillon = 1

    'for each line in "Data":
    For i = 4071 To ligne_Data
        'find which file I should open (mon_objet):
        mon_objet = Sheets("Data").Cells(i, 15).Text        
        'begin to search in "Brouillon" (from the last time where it stopped):
        For j = marque_ligneBrouillon To ligne_Brouillon
            'check the file:
            If InStr(Sheets("Brouillon").Cells(j, 1).Text, mon_objet) <> 0 Then
                'put this file in list:
                opened_Fich = opened_Fich & Sheets("Brouillon").Cells(j, 1).Text & "|"                
                'check the zone:
                If InStr(Sheets("Brouillon").Cells(j, 2).Text, Sheets("Data").Cells(i, 18).Text) <> 0 _
                Or InStr(Sheets("Data").Cells(i, 18).Text, "/") <> 0 Then

                    'in the correct line in "Brouillon" from the beginning till the end:
                    For k = 2 To colonne_Brouillon

                        'check the control whose result I'm looking for:
                        If InStr(Sheets("Brouillon").Cells(j, k).Text, Sheets("Data").Cells(i, 20).Text) <> 0 Then

                            'if this cell contains the result:
                            '1. put the mark here
                            marque_ligneBrouillon = j
                            '2. put the result inside:
                            mon_Data = Sheets("Brouillon").Cells(j, k).Text
                            Sheets("Data").Cells(i, ma_Colonne).Value = mon_Data
                            Flag_j = True
                            Exit For
                        End If

                    Next k

                End If

            End If
            If Flag_j Then Exit For

        Next j
        Flag_j = False

    Next i

    Dim nb_Unique As Long
    'because one txt.file could be opened a several times, so I want to remove the duplicate lines:
    BList = Split(opened_Fich, "|")
    With Worksheets("Brouillon")
        .Range("A1").Resize(UBound(BList)).Value = Application.Transpose(BList)
    End With
    Sheets("Brouillon").Columns(1).RemoveDuplicates Columns:=Array(1), Header:=xlNo
    nb_Unique = Sheets("Brouillon").Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues).Count
    Debug.Print nb_Unique
    
    'then put list L after all these files:
    AList = Split(Fich_dansList, "|")
    With Worksheets("Brouillon")
        .Range("A1").End(xlDown).Offset(1).Resize(UBound(AList)).Value = Application.Transpose(AList)
    End With
    'then remove again all the duplicate parts:
    Sheets("Brouillon").Columns(1).RemoveDuplicates Columns:=Array(1), Header:=xlNo
    'so the ones that haven't been opened are listed after those have been opened:
    nb_total = Sheets("Brouillon").Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues).Count 
    End Sub

Then I'll do the nearly the same thing to put all these haven't been opened files at the end of the table. Because they are the new tests points that have been just added into the test, there is no correct 'place' for them, and I have to complete and improve the table.

    Public Sub Inserer_Nom_Fich()
    ActiveCell.Value = nom_de_Fich
    ActiveCell.Offset(0, 1).Select
    End Sub

<!--- --->

    Public Sub Inserer_contenu()

    Dim keyWord As String

    keyWord = "Zone"
    Inserer_Nom_Fich
    Do While Not EOF(2)
        Line Input #2, contenu
        'if it's a new zone:
        If InStr(contenu, keyWord) < 1 Then
            ActiveCell = contenu
        'if it's not:
        Else                               
            ActiveCell.Offset(1, 0).Select
            ActiveCell.End(xlToLeft).Select
            Inserer_Nom_Fich
            ActiveCell = contenu
        End If

        ActiveCell.Offset(0, 1).Select
    Loop
    ActiveCell.Offset(1, 0).Select
    ActiveCell.End(xlToLeft).Select
    End Sub

<!--- --->

    Public Sub Count_Brouillon()
    ligne_Brouillon = Sheets("Brouillon").UsedRange.Rows.Count
    colonne_Brouillon = Sheets("Brouillon").UsedRange.Columns.Count
    End Sub

It's a little bit too long, so I've tried to add some comments to help you to read. If you've got some ideas to improve and accelerate the program, or something I've not good explained, please leave a comment.