How to delete selected duplicated entries based on colour and or value

[TedX]

Hi, I have a long list of names that gets added to every day and I've formatted it so that every time a duplicate appears, the cell background colour goes

light green, sometimes they are 2 or 3 cells clumped together, and other times just one by itself. I delete the row, by selecting it and right mouse clicking to show a menu and then selecting delete. This works fine except for the following: The original entry higher in the long list, has a set of numeric values, I want to keep, whilst the lower down the list (new entry) duplicate has no value, that the one I want to delete. So I can't just use a filter to select one colour and delete because I have no control over which duplicate will be deleted, probably the highest duplicate on the list which is the exact opposite of what I want to do.

So I have no problem with selecting the duplicates down the bottom of the list and highlighting them (selecting them), except, I can only delete non-stop runs of rows to delete. In the image, I can only delete 9022 by itself, I can delete 9025 to 9027 inclusive in a single action, but I cannot select 9022 and 9025, 9026 and 9027 all at the one time and delete them. At least I can't do it via Excel directly with a mouse, I assume there is a VBA solution but I can't seem to work it out. All help would be appreciated, I suspect this is a few simple lines of VBA code using the colour 198 239 206. Thank you.

 

[alansidman]

will create soon. Stand by.

 

[TedX]

RGB is 198 239 206
Hex is #C6EFCE

I couldn't figure out how to actually do the function, I tried all day, about 10 hours worth, but no luck. So I did it in reverse after watching how to change the font colour based on the color index. So I think the number is 35.

 

[alansidman]

198 239 206

Sub DelBlanks
Dim i as long, lr as long
lr = Range("A" & rows.count).end(xlup).row
For i = lr to 1 step -1
If Range("E" & i).Interior.ColorIndex = RGB(198 239 206) Then
Range("E" & i).EntireRow.Delete
End if
Next i
Msgbox ("Action Completed")
End Sub

 

[alansidman]

Using Color Index 35

Sub DelBlanks
Dim i as long, lr as long
lr = Range("A" & rows.count).end(xlup).row
For i = lr to 1 step -1
If Range("E" & i).Interior.ColorIndex = 35 Then
Range("E" & i).EntireRow.Delete
End if
Next i
Msgbox ("Action Completed")
End Sub

 

[TedX]

198 239 206

Sub DelBlanks
Dim i as long, lr as long
lr = Range("A" & rows.count).end(xlup).row
For i = lr to 1 step -1
If Range("E" & i).Interior.ColorIndex = RGB(198 239 206) Then
Range("E" & i).EntireRow.Delete
End if
Next i
Msgbox ("Action Completed")
End Sub

I got this Compile Error, even before I ran it, the line was in red. The line that started with: If Range("E"

 

[alansidman]

Give me a few minutes to test as it was air code.

 

[TedX]

I ran the script with the colour index of 35, it ran through till the MsgBox came up and said it was completed, but nothing actually happened that I could see. Perhaps 35 isn't the magic number, but when I do have the light green background in the cell, and I have text in it that's 35, the text disappears into the background. A crude test I know but, that's all I've got.

 

[alansidman]

Try this instead.

Sub DelBlanks()
Dim i As Long, lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("E" & i).Interior.Color = RGB(198, 239, 206) Then
Range("E" & i).EntireRow.Delete
End If
Next i
MsgBox ("Action Completed")
End Sub

 

[TedX]

Try this instead.

Sub DelBlanks()
Dim i As Long, lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("E" & i).Interior.Color = RGB(198, 239, 206) Then
Range("E" & i).EntireRow.Delete
End If
Next i
MsgBox ("Action Completed")
End Sub

No change, unfortunately, it ran through till the MsgBox but there were no deletions, every row was still intact and the duplicate colours remained on the rows where duplicates actually existed. If you are getting sick of me, please say so, I really have been more trouble than I'm probably worth.

 

[alansidman]

Not sick. Because I cannot test your color scheme, I suggest you upload a sample spreadsheet to a third party location such as Box.Net or Dropbox.Com so that I can test my code on your actual data. Only 8-12 records in the worksheet.