Deletion of rows in Excel based on colour

[Julio Medallion]

Just wondering if it is possible to delete rows in Excel based on colour?
I have a spreadsheet with 11,000 rows but I need to delete half of them
as the figures in these rows are based on Dec 2010 and are now out of date.

If if highlight and give a certain colour to the cells with Dec 2010 in them would it then be possible to delete the rows with this certain colour in them or does it require more tweaking?

All suggestions welcome.

 

[Gerald Higgins]

If you're willing to manually highlight and colour them, why not manually highlight and delete them instead ?

 

[Julio Medallion]

I am not going to manually highlight them, I would use the find and replace option with wildcard options to select the dates and then format them with a colour. Just wondering if I do this is there then a method of selecting the rows with this colour within them and deleting them? i.e.is this possible or might I just as well ask for tonight's winning Lotto numbers?

Thanks,
Julio

 

[Gerald Higgins]

It is possible, but it sounds like a complicated way of doing it.

There are probably better ways of doing it.

For example, if all the rows to be deleted refer to Dec 2010, and they all contain Dec 2010 somewhere, then one easy way would be to use Data, Filter, to select all the Dec 2010 items, and then delete them.

 

[Julio Medallion]

My added complication is that there are blank lines whne I export the report to Excel so I cannot format it as a table. I actually need to delete about 8,000 of the 11,00 lines in the report if I include the blank lines and the December dates. I'm interested that you said it would be possible, could you offer any suggestions?

Thanks,
Julio

 

[Gerald Higgins]

Julio

You must have some method of identifying the rows you want to delete, whether that's manual, filter, or even random .

My point is that you seem to be adding an un-necessary stage to the process, like this
1) Identify which rows you want to delete
2) Make them all the same colour
3) Delete the rows with that colour

I think you should focus on
1) Identify which rows you want to delete
2) Delete the rows that meet that criteria.

If you absolutely must go down the route of colouring, then deleting, then you can do this through VBA.

This code simply writes the word "BLUE" into any cell in the range a1:a10 that has been coloured a certain shade of blue.

You can adapt it to delete the row, instead of writing in the word "blue".
There may be better ways of doing this . . . .

<TABLE style="WIDTH: 83pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=138 border=0 x:str><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=138><TBODY><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" width=138 height=18>Sub Apply_text_based_on_colour()</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>   Dim Cell As Range</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>   Dim Rng1 As Range</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>   Range("A1:A10").Select</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>   Set Rng1 = Selection</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>   For Each Cell In Rng1</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>       Select Case Cell.Interior.ColorIndex</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>       Case 42</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>           Cell.Value = "BLUE"</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>       End Select</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>   Next</TD></TR><TR style="HEIGHT: 10.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=18>End Sub</TD></TR></TBODY></TABLE>