Help! with Macro - delete entire row
 

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

Help! with Macro - delete entire row
 

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

Help! with Macro - delete entire row
 

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

Help! with Macro - delete entire row
 

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