Deleting unwanted rows

[Kila]

I have a lengthy report I must import into Excel from a legacy Dos-based program. I can import it just fine using column width, but many of the rows have trash in them I do not want. I want to remove rows with headers, page numbers, groups of "------------", etc.

I have written this to delete the MANY empty rows (which works), but I cannot figure out how to write the part to delete the trash rows (starred lines). I am familiar with VBA in access, but this is my first attempt at using it in Excel. My GOAL is to generate rows of clean data only I can paste/import into an access database.

Sub DeleteEmptyRows()

'Delete Empty Rows Macro 

    Dim LastRow As Long, r As Long
    LastRow = ActiveSheet.UsedRange.Rows.Count   'Count all the used rows in the spreadaheet
    LastRow = LastRow + ActiveSheet.UsedRange.Row - 1
    Application.ScreenUpdating = False
    For r = LastRow To 1 Step -1
        If WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r    'If CountA counts Row r & finds that it has not data, it deletes it.
[COLOR="Red"]*    For r = LastRow To 1 Step -1
*        If r.Range("A" & X).Value = "---" Then
*           r.Range("A" & X).EntireRow.Delete
*         End If[/COLOR]
    Next r
End Sub

 

[Galaxiom]

You could preprocess the file to remove the junk it before import. The DOS FINDSTR command is quite good at this task. Many switches are provided to modify which lines are included in the output. It is quite easy to use.

http://www.computerhope.com/findstr.htm

Otherwise the file can be imported by VBA as a text stream object, parsed with InStr and written to a recordset rather than using the import. This is considerably more complex but keeps the whole thing inside Excel.

 

[Galaxiom]

Since it is coming out of DOS it is probably quite appropriate to use the DOS preprocessing.

Also if Access is the final goal then you might as well import directly to Access rather than using Excel at all.