Filter by all colors

[jomili]

I'm using Excel 2010, and have a spreadsheet with columns containing Red, Blue, yellow, and green cells, plus some with no color. If I apply an autofilter, I can filter for any individual color, or for no colors. But how do I do it if I want all colored cells, or to filter out the non-colored?

Thanks in advance,
John

[Solus Rankin]

Specific column? All columns?

[shg]

Color is not really data to Excel. You could use another columns that controls (or is indicative of) the cell color, and then filter on that.

[jomili]

XeRo Solus,
Assume just one column.

SHG,
Sure, I can do it by creating a helper column. I wondered, though, if there was another way.

[Solus Rankin]

Please Login or Register  to view this content.

[jomili]

Cool! Had to change the <> to =, becuase I want to show the colors. Also had to turn off Screenupdating (my worksheet had 12K rows, of which 3 had colored cells).
How would I change it to use the column my active cell is in?

[jomili]

Came up with a workable solution, but I'm certainly open to better ideas:

Please Login or Register  to view this content.

[Solus Rankin]

Please Login or Register  to view this content.

[jomili]

MUCH better job of coding than mine. Thanks! I thought there might be a time difference because I used 'for each' and you didn't, but my clunky version and your streamlined version both do the whole range (12K rows, 3 with colored cells) in 3.03 seconds. I don't know if it could be made faster, but this version certainly works for me. Thanks for all your help!

[max_max]

Hello,
sorry if I intrude in the thread.
The macro Xero Solus does not work for me, where did I go wrong?
Thanks for the control.
max_max
-----------------------------------------------------------------
Ciao,
scusate se mi intromette nel thread.
La macro di Xero Solus non mi funziona, dove ho sbagliato?
Grazie del controllo.
max_max

color 30_08_13.xlsm

[jomili]

Max,
What error are you seeing?

I'm currently putting it through it's paces, and have spotted a problem. My reported run time earlier was with that being the only workbook open. Now I have 5 workbooks open, and when i try to run the macro it just keeps running. I tried using "With Activesheet", but that doesn't seem to have any affect. I have to hit escape to get out of it.

[max_max]

Hello,
I hope that is understandable English, I translate with google translator.
The second macro:

Sub UnfilterColorless ()
UsedRange.EntireRow.Hidden = False
end Sub

does not return rows, maybe it does not work on excel 2007?
max_max
-------------------------------------------------------------------------------
Ciao,
spero che il inglese sia comprensibile, traduco con google traduttore.
La seconda macro:

Sub UnfilterColorless()
UsedRange.EntireRow.Hidden = False
End Sub

non fa ritornare le righe, forse non funziona su excel 2007?
max_max

[jomili]

I had to make a change to mine, maybe you do, too.

Please Login or Register  to view this content.

Also, update on the problem I'm seeing: I closed all the workbooks, reopened just a couple, tried again, and it worked fine. So, not sure what the issue was.

[max_max]

O.k. the macro works,
Now there are 2 macros for a macro that filters all the colored cells as you change?

Sub FilterColorless () 'non-colored cells
Dim cel As Range

For Each cel In Range ("A2: A120" & Range ("A" & Rows.Count). End (xlUp). Row)
If cel.Interior.ColorIndex <> -4142 Then
cel.EntireRow.Hidden = True
end If
Next cel
end Sub

max_max

---------------------------------------------------------------------------------------

O.k. la macro funziona,
Ora ci sono 2 macro, per una macro che filtri tutte le celle colorate come si modifica?

Sub FilterColorless() 'cells non color
Dim cel As Range

For Each cel In Range("A2:A120" & Range("A" & Rows.Count).End(xlUp).Row)
If cel.Interior.ColorIndex <> -4142 Then
cel.EntireRow.Hidden = True
End If
Next cel
End Sub

max_max

[jomili]

There's a filter and an unfilter.

The one you posted, named "Sub FilterColorless () 'non-colored cells", is unnecessary if using Excel 2010, because Excel can already filter for non-colored cells. As well, a macro to filter by a specific color is unnecessary because Excel already has that capability.

This one filters for all of the colored cells regardless of color in a column, which Excel doesn't currently do natively.

Please Login or Register  to view this content.

This one unhides all the rows hidden by the macro above.

Please Login or Register  to view this content.

[max_max]

Hello,
in a forum to excel in Italy I found these macros. They are faster to filter colored cells.
max_max
------------------------------------------------------------------------------------------------
Ciao,
in un forum per excel in Italia ho trovato queste macro. Sono più veloci a filtrare le celle colorate.
max_max

color_B 30_08_13.xlsm

[jomili]

They're actually not any faster on my test data. They only reason they appear faster is that they're only filtering 120 rows with only a few uncolored cells, whereas I'm filtering 12000 rows with only 3 colored cells.

[max_max]

Now is 12000 rows, it is a little slower.
max_max
------------------------------------------
Ora è per 12000 righe, è un pò più lento.
max_max

color_C 30_08_13.xlsm