Delete all rows with yellow or blue

[ekr]

I have rows and rows of information, and some rows are highlight yellow and some highlight blue. i want to delete all rows, except for the ones that are blue or yellow.

[David A Coop]

Hi ekr,

Do you want to do it manually?

You can use the FILTER feature of Excel.

Just set filters for the data range.

Then click on any column with a filter and use the FILTER BY COLOR option, and select No Fill.

Now select all visible rows. (F5; Special...; Visible cells only)

Press Delete.

NB: You could lose your headings, so make sure they are shaded as well.

You can do it by Macro as well, but a lot of people are wary of macros.

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

When you reply please make it clear WHO you are responding to by mentioning their name.

[ekr]

Hello Dave, I am looking for a macro. I have many worksheets in an excel workbook and would like to have a macro that deletes all rows that do not have the color blue or yellow in them.

[ekr]

I want to delete rows that do not contain cells that have a color (rows with colored cells are yellow and blue, but could also be red or green.
So, I want a code that will delete rows that do not contain a colored cell. I used this code but it did not delete any rows. Is colorindex for no color 0 ?

Sub deleterow()
   Dim cell As Range
   For Each cell In Selection
       If cell.Interior.ColorIndex = 0 Then
           cell.EntireRow.Delete
       End If
   Next cell
End Sub

[ekr]

This code deleted all the rows. I'm trying to delete only rows that do not have colored cells.

[Blokeman]

Maybe this, deletes all rows in column A that don't have color.

Sub DeleteRedRows()

  Dim Last As Integer, Del As Integer
Last = Range("A" & Rows.Count).End(xlUp).Row

For Del = Last To 1 Step -1
    If Cells(Del, "A").Interior.ColorIndex = xlNone Then
        Rows(Del).EntireRow.Delete
    End If
Next Del
End Sub

[stnkynts]

I have a suspicion that you are using conditional formatting to color your cells. If that is the case then VBA is going to recognize your colored cells the same as your non colored cells. In other words, VBA and Conditional Formatting do not easily work together. It is possible just extremely challenging. Here is a resource on how to do it. http://www.cpearson.com/excel/cfcolors.htm


Outside of that you could always use a worksheet_change event to color based upon values but that might take a lot of time too depending on how many parameters you have. Best of luck.

[ekr]

The color is just applied using Fill color, and not conditional formatting. Does that help ?

[stnkynts]

Assuming column A:

Sub Delete_NonColoredRows()
Dim lCell As Long, LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

For lCell = LR To 1 Step -1
    If Range("A" & lCell).Interior.ColorIndex = xlNone Then
        Range("A" & lCell).EntireRow.Delete
    End If
Next lCell

End Sub

[ekr]

The code deletes all rows, even rows that contain cells with colorfill. I am not sure why.

[Blokeman]

Put value/text into the cell and color it then run the macro again.