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" wrote:

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"

--
Regards,

OssieMac


"OssieMac" wrote:

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
--
Regards,

OssieMac


"Neon520" wrote:

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