How to delete the row based on the cell color

[ThrottleWorks]

I am using a loop say for example 100 rows.

I am checking the cell color in the range.

If the cell color is <> 1 then delete the entire row


Can anyone please help me in this


The code I am using is given below


For each rn in rng

If rn.interior.color <> 1 then

Delete this row (I am not able to write the line here)

End if

next

 

[sdsurzh]

Hi Sachin,


Try this


Selection.Delete Shift:=xlUp


Thanks,

Suresh Kumar S

 

[ThrottleWorks]

Hi Suresh, thanks for the help, but it is not working for me.


Could you please help.

 

[Debraj]

Hi Sachin,


Can you please try the below for me..

[pre]

Sub sachinbizboy1()
Dim RN As Range
Set RNG = Range("a1:a100")
For Each RN In RNG
If RN.Interior.Color <> 1 Then
RN.EntireRow.Delete xlShiftUp
End If
Next
End Sub

Regards,

Deb


PS 1: I hope you know that.. [code]Interior.Color <> 1

means Deep Ivory Back.. or RGB(0,0,0) and cells are REALLY dark black..

PS 2: I hope you also know that.. starting from top(for example A1), and then if you delete a row(lets say A5) and still not came out from from the loop then A6 will change himself to a5 and your next jump will be in A7.. you will miss A6 which is now A5 and you already checked that cell..


PS: If any of my PS is wrong, you can try the below code..

Sub sachinbizboy2()
Dim RN As Range, RNG As Range
Set RNG = Range("a1:a100&#34
lenth = RNG.Rows.Count
For i = lenth To 1 Step -1
If RNG.Range("A" & i).Interior.Color <> vbYellow Then
RNG.Range("A" & i).EntireRow.Delete xlShiftUp
End If
Next
End Sub[/code][/pre]

 

[sdsurzh]

Thanks Debraj

 

[ThrottleWorks]

Hi Debraj & Suresh, thanks a lot for the help.

I am checking this, will share the results soon.


@Debraj – you are right, I tried some weird loop, & it went infinite.

The color I am using is yellow but the VB code is so long that is why I mentioned black 

 

[ThrottleWorks]

Debraj & Suresh - this has worked, thanks a lot for the help. Have a nice day.