die transpose is eigenlijk overbodig en je loopt vast in het geval dat het aantal rijen uitloopt (+65.500).
Dus nog een beetje korter
Sub Maak_Recept()
Set filekeuze = Application.FileDialog(msoFileDialogFilePicker)
With filekeuze
.InitialFileName = ThisWorkbook.Path & "\"
.Filters.Clear
.Filters.Add "text files", "*.txt"
.AllowMultiSelect = False
If .Show = True Then
tekstfile = .SelectedItems(1)
End If
End With
If tekstfile = "" Then Exit Sub
Open tekstfile For Input As #1
tekst = Split(Input(LOF(1), 1), vbCrLf)
Close #1
ReDim resultaat(1 To UBound(tekst), 1 To 4)
For i = 1 To UBound(tekst)
Select Case LCase(Left(tekst(i), 9))
Case "grondstof": bRecept = True
Case WorksheetFunction.Rept("-", 9): If bRecept Then Exit For
Case Else
If bRecept And Len(tekst(i)) Then
ptr = ptr + 1
resultaat(ptr, 1) = Trim(Left(tekst(i), 35))
resultaat(ptr, 2) = Mid(tekst(i), 37, 8)
resultaat(ptr, 3) = Mid(tekst(i), 46, 14)
resultaat(ptr, 4) = Mid(tekst(i), 61, 7)
End If
End Select
Next i
With Sheets("recept")
.Columns("A:D").ClearContents
.Cells(1, 1).Resize(, 4) = Array("Grondstof", "Artikel", "Gewicht", "%")
.Cells(2, 1).Resize(ptr, 4) = resultaat
.Columns("A:D").EntireColumn.AutoFit
End With
End Sub