PDA

View Full Version : Delete the row if cell contains null value else as it is ?



aligahk06
11-01-2009, 05:02 AM
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
11-01-2009, 06:00 AM
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

aligahk06
11-01-2009, 06:50 AM
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


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
11-01-2009, 01:45 PM
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
11-01-2009, 10:44 PM
Soory, I didn't know the rule? I will not repeat in future.
Thanks for your time.