Delete Rows Based on highlighted Color

[Neon520]

Hi Everyone,

Does anyone know a way to Delete Rows based up the row or cell color of that
row?

I'm working on a huge spreadsheet, about 3 to 4 thousands row, and there's a
lot of rows that I need to get rid of base on the highlighted color. It's
really time consuming to manually delete all those rows.

Can anyone tell me if this is possible in Excel?

Thank you,
Neon520

 

[OssieMac]

The following macro will delete rows based on the color of a cell that you
select.

I have assumed the following:-
That the color refers to the Interior color (that is background color) not
the font color. If this is not correct then do not use the code and advise me.

That all colors are the same. If not then you might have to re-run the code
for each color. The code can be modified to simply delete all rows with no
color. If required this way then let me know.

That the entire row is colored although the code identifies only the cells
in the column in which you select the colored cell.

The code will ask you to select a cell with the color to be deleted. Simply
click on a single cell containing the color and click OK. You can select
Cancel to abort the operation at this point.

Because you are deleting rows, ensure that you backup your workbook in case
the code does not do exactly what you want.

Sub DeleteRows()

Dim rngColorCode As Range
Dim lngColorCode As Long
Dim lngColumn As Long
Dim i As Long

On Error Resume Next
Set rngColorCode = Application.InputBox _
(Prompt:="Select a cell with the interior color to be deleted", _
Title:="Color Selection", Type:=8)
On Error GoTo 0

If rngColorCode Is Nothing Then
MsgBox "User cancelled operation." & vbCrLf & _
"Processing terminated"
Exit Sub
End If

lngColumn = rngColorCode.Column

lngColorCode = rngColorCode.Interior.Color

With ActiveSheet.UsedRange
'Must work backwards from bottom when deleteing rows.
For i = .Rows.Count To 1 Step -1
If .Cells(i, lngColumn).Interior.Color = lngColorCode Then
.Rows(i).Delete
End If
Next i
End With

End Sub

 

[OssieMac]

Replace the following text in my expanation:-
"The code can be modified to simply delete all rows with no color."

with the following:-
"The code can be modified to simply delete all rows EXCEPT rows with no color"

 

[Neon520]

Hi OssieMac,

Thank you SO much for replying my post. I'm thought this is impossible in
Excel, but looks like your code really work.

Answer to your assumption:
1. Yes, I'm referring to the "FILL" color of each cell.
Is it also possible to do it base on "FONT" color? (just curious)

2. Yes and No, see my question below. If I would have two set of color
identifying two different status and I want to delete both of those, I would
have to just run the code twice, right? (EX: Red highlight for Terminated
record, Orange for On Hold, I can just run the code twice to delete both
records set, right?)

3. Would it matter if ONLY the first column is colored and the rest of the
cells in such row are not.


A few clarifying questions:
1. Will your code still delete the Entire row if only the first column in
the row is colored, and that's the color I select when prompted?

2. What does the code react if let's say most of the cells in the row are
RED, and a few are Different color, and when the code prompt, I select RED?
Will the code still delete such Entire regardless the few Different Code,
cause this is what I want.

Thank you SO MUCH. You're awesome!

Neon520

 

[Neon520]

Hi OssieMac,

Since your response for this post works so amazingly well. Is it possible
for you to take a look at another challenge question that I have for the same
project I'm working on?

The title of the post is "Transpose Based on Criteria."

Your response is greatly appreciated.

Neon520

 

[OssieMac]

I've copied your questions to this post because I think it is easier that way
to answer.

EX: Red highlight for Terminated record, Orange for On Hold, I can just run
the code twice to delete both records set, right?

Yes. run the code for each color.

3. Would it matter if ONLY the first column is colored and the rest of the
cells in such row are not.

No. It does not matter which column the color is in. However, the code looks
for the color ONLY IN THE SAME COLUMN where you select the color but it
deletes the entire row irrespective of what other colors are in that row.
Therefore if you select Yellow say in a cell column C and another row has
cell in column A that is yellow and the cell in column C is not yellow then
the row will not be deleted. It is possible to write code to test every cell
in every row and delete the row if the code finds a match. If you are going
to be running this code often then it is possibly worth the effort but if it
is a one off thing then not worth the effort.


A few clarifying questions:
1. Will your code still delete the Entire row if only the first column in
the row is colored, and that's the color I select when prompted?

Yes just so long as you select the color in a cell in the first column as
per my previous answer.

2. What does the code react if let's say most of the cells in the row are
RED, and a few are Different color, and when the code prompt, I select RED?
Will the code still delete such Entire regardless the few Different Code,
cause this is what I want.

Yes just so long as it finds the selected color in the column in which you
selected the color.

If you really need modifications to the code to test every cell in every row
then let me know.

i have replied to your other question asking for more info.

 

[Neon520]

Thank you for your clarification.
One strange thing I notice when I run the code, not quite follow your
explanation.
After running the code, I do a save as to compare the result. Let's say Row
2 is highlighted in RED and RED is the color I selected when prompt,
according to your explanation, the Entire row 2 should be deleted and Row 3
that is in YELLOW should move up Row 2. But what happen was Row 3 move to Row
2 as well as other subsequent rows, but the ONLY the columns with data (let's
say A-D) stay in YELLOW and the rest of the columns from E on are in RED. Can
you explain this strange outcome? Could AutoFilter play any role in this?

One more curious question if you don't mind.
If the data in Excel is not uniform, is it possible to write a code to copy
the data and put them back on a unified matter?
Let's me explain a bit for what I mean by "Uniform" - if someone would have
to Consolidate two different spreadsheet into one, and without matching the
Header Row, the data from Spreadsheet 2 just got copied to the end of
Spreadsheet 1, so data from SpSh 2 is not necessarily match the header row.
Is it possible to write a code to match them back up? I'm talking about data
from multiple spreadsheets being "jumble up" together here.

I don't think that Excel is powerful enough to do this, but please correct
me if I'm wrong.

Best Regards,
Neon520

 

[OssieMac]

You should not have AutoFilter applied when you run the code. However, try
the following code with a modification that tests all cells in each row for
the selected color and if it finds one matching the selected color then the
entire row is deleted.

Sub DeleteRows()

Dim rngColorCode As Range
Dim lngColorCode As Long
Dim i As Long
Dim j As Long

On Error Resume Next
Set rngColorCode = Application.InputBox _
(Prompt:="Select a cell with the interior color to be deleted", _
Title:="Color Selection", Type:=8)
On Error GoTo 0

If rngColorCode Is Nothing Then
MsgBox "User cancelled operation." & vbCrLf & _
"Processing terminated"
Exit Sub
End If

lngColorCode = rngColorCode.Interior.Color

With ActiveSheet.UsedRange
'Must work backwards from bottom when deleting rows.
For i = .Rows.Count To 1 Step -1
For j = 1 To .Columns.Count
'Test each cell in the row
If .Cells(i, j).Interior.Color = lngColorCode Then
.Rows(i).Delete
Exit For
End If
Next j
Next i
End With

End Sub