Need Help cleaning up Macro that imports text file and deletes unnecessary rows

So I am completely new to VBA (like just started yesterday and have no programming experience) and was trying to create a macro that took a text file and converts it into an excel worksheet. With the aid of the internet, I was able to piece together the following code get it to work after spending an afternoon looking things over. However, I feel like there might be a more efficient way of handling the row deletion as the worksheet hangs for a bit and eventually gives me the "Error Running Macro" message. Does anyone have any suggestions or see anything that is unnecessary? Appreciate the help!

Sub CustomerStatements()

Dim TestRow As Range
Dim LR As Long, i As Long

DestFile = Application.GetOpenFilename   'Opens Popup that allows me to choose a text file that will use the fixed width parameters below.
If DestFile = False Then
msg = MsgBox("No file was selected")
Exit Sub
End If
On Error GoTo ErrHandler
Workbooks.OpenText Filename:=DestFile, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(7, 1), Array(21, 1), Array(33, 1), Array(59, 1), Array(75, 1), Array(80, _
        1)), TrailingMinusNumbers:=True 'Creates columns from text file based on the above Fixed width settings
On Error GoTo 0

Cells.Select
Selection.Columns.AutoFit
ActiveWindow.Zoom = 85
Rows("1:25").Delete 'Deletes First 25 Rows
Range("A1").CurrentRegion.Select
LR = Range("A" & Rows.Count).End(xlUp).Row 'Deletes any row that that doesn't have a date in the first column
For i = LR To 1 Step -1
    If Not IsDate(Range("A" & i).Value) Then Rows(i).Delete
Next i 'I feel like there might be a more efficient coding as I get the error messagebox below every time I run this

ErrHandler:
msg = MsgBox("Error Running Macro")
End Sub

Hello ppilot,

The problem is when the For Next loop completes, the ErrHandler code is next in line to execute. Since there are no conditions to control its execution, the message is always displayed.

I have changed the handler to check if there is an error. If an error is detected, the error number and description are included in the message.

Sub CustomerStatements()

    Dim TestRow As Range
    Dim i       As Long
    Dim LR      As Long

        DestFile = Application.GetOpenFilename   'Opens Popup that allows me to choose a text file that will use the fixed width parameters below.
        If DestFile = False Then
            msg = MsgBox("No file was selected")
            Exit Sub
        End If
        
        On Error GoTo ErrHandler
        
            Workbooks.OpenText Filename:=DestFile, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
                    Array(0, 1), Array(7, 1), Array(21, 1), Array(33, 1), Array(59, 1), Array(75, 1), Array(80, 1)), _
                    TrailingMinusNumbers:=True 'Creates columns from text file based on the above Fixed width settings

            Cells.Select
            Selection.Columns.AutoFit
            ActiveWindow.Zoom = 85
            Rows("1:25").Delete 'Deletes First 25 Rows
            Range("A1").CurrentRegion.Select
            LR = Range("A" & Rows.Count).End(xlUp).Row 'Deletes any row that that doesn't have a date in the first column
            
            For i = LR To 1 Step -1
                If Not IsDate(Range("A" & i).Value) Then Rows(i).Delete
            Next i 'I feel like there might be a more efficient coding as I get the error messagebox below every time I run this

ErrHandler:
If Err <> 0 Then
    MsgBox "Error Running Macro - error:'" & Err.Number & "'" & vbCrLf & vbCrLf & Err.Description
End If

End Sub

Much appreciated! I am still getting a handle on being able to interpret the flow of VBA code, but that makes sense. As for the row deletion process, is there a more efficient way of handling it is the lf not loop good enough?

Hello ppilot,

It really depends on how your data is arranged. One method which is faster is to create a combined range of all the rows to be deleted and then delete the rows all at once.

Another method involves using an array. The contents of the range are read into the array if they are not to be deleted. The original range is cleared and the array is copied back. This method is very quick provided you don't need to copy cell formatting.

I copy and pasted two line items from the text file as an example of the data arrangement:

(Date) (Text and Number) (Number1) (Number2) (Number3) (Number4)
(Number5)

01Mar13 XXX IV-999999 123456-859 2,517.64 2,517.64 122
159866
16Apr13 XXX IV-999998 123457-978 1,798.19 1,798.19 76
161654

The second line is deleted along with the page headers so I end up with the first line broken out into six different columns. Sometimes the text file can be up to 20 pages long. Does that help?

For some reason the message board is pushing (Number5) over to the very left when it should be indented slightly under the (text and number).

Hello ppilot,

In this case, using the array would be the best. It will eliminate the need to make a second pass and delete rows. I am working on the code for you.

Hello ppilot,

Almost done. I have a question about the dates. do they appear like 01Mar13 or like 01-Mar-13?

Hello ppilot,

Here is the macro using an array to create a contiguous block of data. It is a bit long mostly due to all the comments. I thought you may want to understand what the code is doing.

Sub CustomerStatements()

  ' Thread: http://www.excelforum.com/excel-programming-vba-macros/939617-need-help-cleaning-up-macro-that-imports-text-file-and-deletes-unnecessary-rows.html
  
    Dim byteData()  As Byte
    Dim Data()      As Variant
    Dim DestFile    As String
    Dim dmyDate     As Variant
    Dim LineData    As Variant
    Dim n           As Long
    Dim Rng         As Range
    Dim Text        As String
  
  
      ' Opens Popup that allows me to choose a text file that will use the fixed width parameters below.
        DestFile = Application.GetOpenFilename
            If DestFile = False Then
                MsgBox "No file was selected"
                Exit Sub
            End If
       
        Set Rng = Range("A1:G1", Cells(Rows.Count, "A").End(xlUp))
        
      ' Read the entire file as a byte array.
        Open "C:\Users\Owner\Documents\Test File.txt" For Binary Access Read As #1
            ReDim byteData(LOF(1))
            Get #1, , byteData
        Close #1

      ' Convert the byte array into text.
        Text = StrConv(byteData, vbUnicode)
        
      ' Remove the excess spaces between the fixed widths.
        Text = Application.Trim(Text)
        
          
        ReDim Data(0)
        
        If Text <> "" Then
          ' Put the individual fields into an array.
            LineData = Split(Text, " ")
            
          ' Convert the first field to a date in DMY format VBA understands.
            dmyDate = Left(LineData(0), 2) & "-" & Mid(LineData(0), 3, 3) & "-" & Right(LineData(0), 2)
            
          ' Add the line of data to the output array if the date is DMY and all fields are present.
            If IsDate(dmyDate) And UBound(LineData) = 6 Then
                LineData(0) = dmyDate
                Data(n) = LineData
                n = n + 1
                ReDim Preserve Data(n)
            End If
        End If
        
      ' Clear all the rows and copy the file data to the worksheet.
        Rng.Clear
        Rng.Resize(RowSize:=n).Value = Data
        
      ' Delete the first 25 rows.
        Rng.Rows("1:25").EntireRow.Delete
        
      ' Adjust the column widths.
        Rng.Columns.AutoFit
        
End Sub

Hello ppilot,

That was my assumption. Just wanted to be sure. Thanks.