Assistance needed with existing code on importing multiple data files into Excel

[sam813]

I found the code below online and was able to tweak it a little to suit my needs. It basically imports all of the data out of a list of data files located within a directory. The problem is that at the end of the imported data, there are about 20 rows of unrecognizable text. Here is a screenshot on Imgur of what I see right after the last row of data:

Here is the code that I found. For the most part it seems to work and I can manually delete the rows of unrecognizable text but preventing it from appearing would be nice.

Sub ReadFilesIntoActiveSheet()
    Dim fso As FileSystemObject
    Dim folder As folder
    Dim file As file
    Dim FileText As TextStream
    Dim TextLine As String
    Dim Items() As String
    Dim i As Long
    Dim cl As Range

    ' Get a FileSystem object
    Set fso = New FileSystemObject

    ' get the directory you want
    Set folder = fso.GetFolder("C:\Ded\")

    ' set the starting point to write the data to
    Set cl = ActiveSheet.Cells(1, 1)

    ' Loop thru all files in the folder
    For Each file In folder.Files
        ' Open the file
        Set FileText = file.OpenAsTextStream(ForReading)

        ' Read the file one line at a time
        Do While Not FileText.AtEndOfStream
            TextLine = FileText.ReadLine

            ' Parse the line into , delimited pieces
            Items = Split(TextLine, ",")

            ' Put data on one row in active sheet
            For i = 0 To UBound(Items)
                cl.Offset(0, i).Value = Items(i)
            Next

            ' Move to next row
            Set cl = cl.Offset(1, 0)
        Loop

        ' Clean up
        FileText.Close
    Next file

    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing

End Sub

 

[Michael M]

are there any blank cells in the data in column "A" after it is Imported ??
Are the same amount of columns used for each row of the data ??

 

[sam813]

are there any blank cells in the data in column "A" after it is Imported ??
Are the same amount of columns used for each row of the data ??

No for the first question.
Yes for the second question.

 

[Michael M]

Try....UNTESTED

Sub ReadFilesIntoActiveSheet()
    Dim fso As FileSystemObject
    Dim folder As folder
    Dim file As file
    Dim FileText As TextStream
    Dim TextLine As String
    Dim Items() As String
    Dim i As Long
    Dim cl As Range

    ' Get a FileSystem object
    Set fso = New FileSystemObject

    ' get the directory you want
    Set folder = fso.GetFolder("C:\Ded\")

    ' set the starting point to write the data to
    Set cl = ActiveSheet.Cells(1, 1)

    ' Loop thru all files in the folder
    For Each file In folder.Files
        ' Open the file
        Set FileText = file.OpenAsTextStream(ForReading)

        ' Read the file one line at a time
        Do While Not FileText.AtEndOfStream
            TextLine = FileText.ReadLine

            ' Parse the line into , delimited pieces
            Items = Split(TextLine, ",")

            ' Put data on one row in active sheet
            For i = 0 To UBound(Items)
                cl.Offset(0, i).Value = Items(i)
            Next

            ' Move to next row
            Set cl = cl.Offset(1, 0)
        Loop

        ' Clean up
        FileText.Close
    Next file

    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
Dim lr As Long, r As Long, lc As Integer
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To lr - 25 Step -1
If Cells(r, lc) < 9 Then Rows(r).Delete
Next r
End Sub