Help me clean up file

[micko_escalade]

Hi all!

I need to clean out unnecessary data from a file,
(see this example text file Link),
I'm not sure how to go about this in excel,

basically every file starts with 9 cells that needs to be deleted, then
two cells of real data then one with garbage that needs to be deleted, it goes like that for 40 cells,
then again 10 cells of garbage that needs to be deleted, then
40 of real data and goes like that up to 3000 lines,

I know it sounds confusing but if you take a look at linked file from above it will make more sense, (to save it right click on "clean up.txt" and then save target as)

at the end I need to have
all cells full of data

any help is appreciated!

 

[micko_escalade]

This is sample file but real is the same, just much longer with same pattern
Check it out after I apply the code http://www.speedyshare.com/448416172.html
before I apply the code http://www.speedyshare.com/915612401.html

 

[jindon]

Or if you have multiple .txt files in the same folder and read them all in the same manner then try

Sub test()
Dim a(), fn As String, ff As Integer, txt As String, 
Dim i As Long, n As Long, t As Long, myDir As String
myDir = "c:\test\"   '<--- alter to suite
If Dir(myDir, vbDirectory) = "" Then
     MsgBox "Path is wrong"
     Exit Sub
End If
ff = FreeFile
fn = Dir(myDir & "*.txt")
Do While fn <> ""
     Open myDir & fn For Input As #ff
     Do While Not EOF(ff)
          Line Input #ff, txt
          i = i + 1      
          If i > 9 Then
               n = n + 1
               If n <=44 And n Mod 3 <> 0 Then
                    t = t + 1
                    ReDim Preserve a(1 To t) : a(t) = txt
               End If
               If n = 54 Then n = 0
          End If
     Loop
     Close #ff
     With ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1)
          .Resize(t).Value = Application.Transpose(a)
     End With
     i = 0 : n = 0 : t = 0
     fn = Dir()
Loop
End Sub

 

[micko_escalade]

I tried code for the folder and I got "Can't execute code in break mode"

 

[jindon]

Save the book and open again.

BTW, if you want leave all the numerical lines then run the code after you inport the files to Excel

Sub sample()
On Error Resume Next
With ActiveSheet.Columns("a")
     .SpecialCells(2,2).EntireRow.Delete
End With
End Sub

 

[micko_escalade]

Thanks jindon!!!!!

that work around is way better and more simple,

after running your last suggestion code I pressed F5 then Special then selected blanks after I deleted selected blanks just thought it might be useful for some later on

I managed to accomplish what I needed to do.