Delete the row if cell contains null value else as it is ?

[aligahk06]

Dear All,

Greetings of the day!!!!!
In my excel workbook a column consist the following records.
i.e In Col I record in each individual cell is pipe,flange, valve reducer, tee, ELL.
Sample is for ref.
if the cell contains in I is pipe,flange, valve reducer, tee,or ELL then leave that row as it is
else delete the row.
The search start from beggining to end.
Attachment is for reference.
Is there any macro to do this task ?

Please assist ?

Rgds,
Aligahk06

[mdmackillop]

[VBA]
Option Explicit
Option Compare Text
Sub Data()
Dim Arr, a
Dim Rng As Range, cel As Range
Dim i As Long
Dim Del As Boolean
Arr = Array("PIPE", "ELL", "TEE", "REDUCER", "VALVE", "CAP")
Set Rng = Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp))
For i = Rng(Rng.Cells.Count).Row To Rng.Cells(1).Row + 1 Step -1
Del = True
For Each a In Arr
If InStr(1, Cells(i, 1), a) > 0 Then
Cells(i, 6) = Cells(i, 3)
Cells(i, 7) = Cells(i, 4)
Cells(i, 9) = a
Del = False
End If
Next
If Del Then Cells(i, 1).EntireRow.Delete
Next
End Sub
[/VBA]

[aligahk06]

[VBA]
Option Explicit
Option Compare Text
Sub Data()
Dim Arr, a
Dim Rng As Range, cel As Range
Dim i As Long
Dim Del As Boolean
Arr = Array("PIPE", "ELL", "TEE", "REDUCER", "VALVE", "CAP")
Set Rng = Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp))
For i = Rng(Rng.Cells.Count).Row To Rng.Cells(1).Row + 1 Step -1
Del = True
For Each a In Arr
If InStr(1, Cells(i, 1), a) > 0 Then
Cells(i, 6) = Cells(i, 3)
Cells(i, 7) = Cells(i, 4)
Cells(i, 9) = a
Del = False
End If
Next
If Del Then Cells(i, 1).EntireRow.Delete
Next
End Sub
[/VBA]

Dear Sir,
Can u explain how the code is working.
I am a little bit confused about the syntax and flow.
Please explain?
Help is deeply welcome.


Rgds,
Aligahk06

[Simon Lloyd]

Aligahk06, please make sure you post the links here to all your cross posts, this means adding the url of each and every forum that you posted this question in, you have already had a formula based answer in the Microsoft Newsgroups!

Read the cross post link in my signature for an explanation why.

[aligahk06]

Soory, I didn't know the rule? I will not repeat in future.
Thanks for your time.