Hi,
I am downloading data from AS/400 in text format on a daily basis and it is very pains taking to clean the headers as it is over 5000 lines.

Report xyz SUMMARY
2/22/06 PAGE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Fc Cls No Type No UM UOM Cur Cost QTY
================================================== =====================================
TT GP 02684 B 10000 xx xxx xxxxxxxxxx x CA CA CAD 26.02000 1256.000
TT GP 02685 B 10000 xx xxx xxxxxxxxxx x CA CA CAD 23.78000 540.000

Can any one suggest a macro which can do the following

Search line by line for words like "Report", "Page", " =========" etc etc and del the entire row (i meam every thing in that row)
In the end sort the data by column "A"

Thanks,

Rashid

Does this idea help?

Sub cleanuprows()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
If Left(Cells(i, 1), 6) = "Report" Or _
InStr(Cells(i, 1), "PAGE") 0 Or _
InStr(Cells(i, 1), "==") 0 Then
Rows(i).Delete
End If
Next i
End Sub

--
Don Guillett
SalesAid Software

"Rashid" wrote in message .. .
Hi,
I am downloading data from AS/400 in text format on a daily basis and it is very pains taking to clean the headers as it is over 5000 lines.

Report xyz SUMMARY
2/22/06 PAGE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Fc Cls No Type No UM UOM Cur Cost QTY
================================================== =====================================
TT GP 02684 B 10000 xx xxx xxxxxxxxxx x CA CA CAD 26.02000 1256.000
TT GP 02685 B 10000 xx xxx xxxxxxxxxx x CA CA CAD 23.78000 540.000

Can any one suggest a macro which can do the following

Search line by line for words like "Report", "Page", " =========" etc etc and del the entire row (i meam every thing in that row)
In the end sort the data by column "A"

Thanks,

Rashid

This works,

You will have to change the letter column letter to suit

Dim LastRow As Long
Dim RowNdx As Long
Dim OldVal As String

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Left(Cells(RowNdx, "A"), 6) = "Report" Then
'The length of the word is 6 the word is report if you want to remove ====
you then need to change 6 to 4
Rows(RowNdx).Delete
End If
Next RowNdx

If you have any problems email me @



And I will assist further
HTH

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

Assuming Cost is in Column 10, and the word Cost is in Row 3

Sub ClearBad()
Dim
v = Rows(3)
columns(10).Specialcells(xlbanks).EntireRow.Delete
Columns(10).Specialcells(xlconstants,xlTextValues) .EntireRow.Delete
Rows(1).Insert
Rows(1).Value = v
End Sub

may work. Adjust to fit

--
Regards,
Tom Ogilvy



"Rashid" wrote in message
.. .
Hi,
I am downloading data from AS/400 in text format on a daily basis and it is
very pains taking to clean the headers as it is over 5000 lines.

Report xyz SUMMARY
2/22/06 PAGE
---------------------------------------------------------------------------
----------------------------------------------------------------------------
---------------------
Fc Cls No Type No UM UOM
Cur Cost QTY

================================================== ==========================
===========
TT GP 02684 B 10000 xx xxx xxxxxxxxxx x CA CA
CAD 26.02000 1256.000
TT GP 02685 B 10000 xx xxx xxxxxxxxxx x CA CA
CAD 23.78000 540.000

Can any one suggest a macro which can do the following

Search line by line for words like "Report", "Page", " =========" etc etc
and del the entire row (i meam every thing in that row)
In the end sort the data by column "A"

Thanks,

Rashid