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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.