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]

Welcome TedX
We cannot manipulate data in a picture. Please reload your sample using XL2BB

 

[TedX]

Welcome TedX
We cannot manipulate data in a picture. Please reload your sample using XL2BB

Thanks, Alansidman, but there is no code per see, it's just a list of names, straight-out text. I just want to delete more than one row at a time without using sort or filter. I want to visually select the rows I want to delete and then delete them in one go. As I explained, Excel doesn't permit this, I can select some rows and delete them, but not select them all if there are gaps in the rows, meaning that I want to keep some rows. This should be really simple. The question is, why can't I select rows of my choice and delete them all at the same time? I will admit that there is code in the columns that have numeric output, if that helps. I did look at adding XL2BB but couldn't find it in my add-ins, sorry.

 

[TedX]

Welcome TedX
We cannot manipulate data in a picture. Please reload your sample using XL2BB

I persisted and think I got XL2BB working, I hope I haven't wasted your time Alansidman.

XbetForm.xlsm
	A	B	C	D	E	F	G	H	I	J	K																	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
9110	69.01	70.01	-1.00	70.01	INTHELAPOFTHEGODS	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		INTHELAPOFTHEGODS	62
9111	69.01	70.01	-1.00	70.01	SVANEKE	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		SVANEKE	62
9112	69.01	70.01	-1.00	70.01	COGENT	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		COGENT	57
9113	69.01	70.01	-1.00	70.01	LAVA	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		LAVA	56
9114	69.01	70.01	-1.00	70.01	WIESENBACH	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		WIESENBACH
9115	69.01	70.01	-1.00	70.01	MORNINGTON MASK	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		MORNINGTON MASK	58
9116	69.01	70.01	-1.00	70.01	MY BUTTONS BIGGER	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		MY BUTTONS BIGGER	54
9117	69.01	70.01	-1.00	70.01	SASSOON	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		SASSOON	51
9118	69.01	70.01	-1.00	70.01	RAJPIPLA	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		RAJPIPLA	48
9119	69.01	70.01	-1.00	70.01	VERDI	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		VERDI	47
9120	-1.00		-1.00	0.00	Fake name 1			2.00		2																		0.00	7.00	-5.00	1.00		Fake name 1
9121	-1.00		-1.00	0.00	COGENT			2.00		2																		0.00	7.00	-5.00	1.00		COGENT
9122	-1.00		-1.00	0.00	Fake name 2			2.00		2																		0.00	7.00	-5.00	1.00		Fake name 2
9123	-1.00		-1.00	0.00	WIESENBACH			2.00		2																		0.00	7.00	-5.00	1.00		WIESENBACH
9124	-1.00		-1.00	0.00	MORNINGTON MASK			2.00		2																		0.00	7.00	-5.00	1.00		MORNINGTON MASK
9125	-1.00		-1.00	0.00	Fake name 3			2.00		2																		0.00	7.00	-5.00	1.00		Fake name 3
9126	-1.00		-1.00	0.00				2.00		2																		0.00	7.00	-5.00	1.00		0.00
9127	-1.00		-1.00	0.00				2.00		2																		0.00	7.00	-5.00	1.00		0.00
Rating

Cell Formulas
Range	Formula
AB9110:AB9127	AB9110	=$D9110
AC9110:AC9127	AC9110	=SUM($H9110+5)
AD9110:AD9127	AD9110	=SUM($D9110-5)
AE9110:AE9127	AE9110	=SUM($D9110+1)
A9110:A9127	A9110	=SUM($C9110:$D9110)
D9110:D9127	D9110	=$B9110
H9110:H9127	H9110	=SUM($J9110:$K9110)
AG9110:AG9127	AG9110	=[@[Horse (Xbet)]]
AJ9110:AJ9127	AJ9110	=IFERROR(VLOOKUP(Table8[@HorseNames],Benchmark!A:B,2,FALSE),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AJ:AJ	Cell Value	=0	text	NO
E15160:E1048576,E8173:E15157,E7725:E8167,E1:E4505,E4508:E7723	Cell Value	duplicates	text	NO

 

[alansidman]

I believe I understand what you want. With your cursor on the row number, click on Enter and at the same time hold down the CTL button. Now with the CTL button still depressed, select the next row to delete and continue down. When all have been selected. Click on the Delete button.

 

[TedX]

I believe I understand what you want. With your cursor on the row number, click on Enter and at the same time hold down the CTL button. Now with the CTL button still depressed, select the next row to delete and continue down. When all have been selected. Click on the Delete button.

Thanks again Alansidman, I tried the multiple select the way you said and indeed I have been doing that for quite some time (so you are on the money), however, it doesn't work on this specific worksheet. I copied and pasted the data into a new worksheet and it works perfectly, the rows delete as hoped. It's as if this worksheet has some error protection built in that I'm not aware of, and it just stops me from deleting non-contiguous rows, once there is a regular row I don't want to delete between them, the delete function greys out. On a replicated new page, the delete stays active and I can select any combination of rows and delete them. There is something different about this specific page. I built that page over the last three years and have not consciously protected it in any way. I do have Vlookups to other worksheets and they are wrapped in Iferror but that is just straight-out Excel that works everywhere else.

Okay, not to let this drop, I have just made a discovery, I have always used my mouse, both left and right buttons extensively, in fact, I hardly ever use a keyboard (my bad), so I just selected the desired rows and actually hit the Delete key on my wireless keyboard and the contents of the rows deleted, this is a major breakthrough for me. I now have to just figure out the best way to close up the blank (now empty rows). Probably a combination of filtering out the blanks I guess, or sorting the entire table so the empty rows should go to the bottom. I'll play around with these ideas but I'd stumped as to why I get a different response using the mouse as opposed to the keyboard, but I am happy and I thank you very much. If you can think of a better way of closing the blank rows 9268, 9270, 9271, 9273, 9275 and 9276 that would be great. Thanks again Alansidman, you are a cool bloke

XbetForm.xlsm
	A	B	C	D	E	F	G	H	I	J	K																	AB	AC	AD	AE	AF	AG	AH
9265	69.01	70.01	-1.00	70.01	PROPHET'S CHOICE	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		PROPHET'S CHOICE
9266	69.01	70.01	-1.00	70.01	OUR ELLIE ROSE	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		OUR ELLIE ROSE
9267	69.01	70.01	-1.00	70.01	LADY ANNABEL	70.01		72.01	70.01	2	70.01																	70.01	77.01	65.01	71.01		LADY ANNABEL
9268
9269	-1.00		-1.00	0.00	Fake 1			2.00		2																		0.00	7.00	-5.00	1.00		Fake 1
9270
9271
9272	-1.00		-1.00	0.00	Fake 2			2.00		2																		0.00	7.00	-5.00	1.00		Fake 2
9273
9274	-1.00		-1.00	0.00	Fake 3			2.00		2																		0.00	7.00	-5.00	1.00		Fake 3
9275
9276
Rating

Cell Formulas
Range	Formula
AB9265:AB9267,AB9274,AB9272,AB9269	AB9265	=$D9265
AC9265:AC9267,AC9274,AC9272,AC9269	AC9265	=SUM($H9265+5)
AD9265:AD9267,AD9274,AD9272,AD9269	AD9265	=SUM($D9265-5)
AE9265:AE9267,AE9274,AE9272,AE9269	AE9265	=SUM($D9265+1)
A9265:A9267,A9274,A9272,A9269	A9265	=SUM($C9265:$D9265)
D9265:D9267,D9274,D9272,D9269	D9265	=$B9265
H9265:H9267,H9274,H9272,H9269	H9265	=SUM($J9265:$K9265)
AG9265:AG9267,AG9274,AG9272,AG9269	AG9265	=[@[Horse (Xbet)]]

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E15160:E1048576,E7725:E8167,E1:E4505,E4508:E7723,E8173:E15157	Cell Value	duplicates	text	NO

 

[alansidman]

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 IsEmpty(Range("A" & i).Value) = True Then
Range("A" & i).EntireRow.Delete
End if
Next i
Msgbox ("Action Completed")
End Sub

This is some VBA aircode that you could use to remove the blank rows. This assumes that if Column A is blank, then the entire row will be deleted. Be sure to back up your file before testing.

How to install your new code

• Copy the Excel VBA code
• Select the workbook in which you want to store the Excel VBA code
• Press Alt+F11 to open the Visual Basic Editor
• Choose Insert > Module
• Edit > Paste the macro into the module that appeared
• Close the VBEditor
• Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

• Press Alt-F8 to open the macro list
• Select a macro in the list
• Click the Run button

 

[TedX]

WOW Alansidman, thank you very much. I put the macro into my Personal workbook because I believe I will use this on more than just this current project, it's actually a brilliant bit of kit. I made an example and showed the wife, it was a stellar demonstration. We discussed the time it takes me to click on (select) each row, as my part of the contribution. We figured that if your macro was to select the duplicate (light Green) horse names in column E and then call your DelBlanks macro, that would be the absolute ideal situation.

My scenario in English would be to grab the new rows from the source worksheet and paste them on the last row of the current table (I'm already doing this). Then the current formatting would automatically show the duplicates in light green background cell colour. Then a new macro to select the rows that are duplicated. Then either me physically hit the delete key on the keyboard or for that to be done via VBA. Then a call to your macro to delete the deleted rows.

Is there any way that via VBA you can select each row based on the cell background colour in column E?

 

[alansidman]

Simple answer is Yes. Would need to know that the color index number is.

Click on one of the green cells and run this macro. Then advise what the color index number is.

Function GetColor(r As Range) As Integer
GetColor = r.Interior.ColorIndex
End Function

Put your cursor on one of the cells colored not by conditional formatting.
Click on the Function Icon (fx)
and click enter.

 

[TedX]

RGB is 198 239 206
Hex is #C6EFCE