FilterByColour - Code Improvement Suggestions

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
Hi All,

I've written a Macro, to Filter by Colour, however I'm sure there's a better way of doing it, please advise.
Sub FilterByColour()
' Written 24/03/2008 by MRdNk

Dim lFilterColour As Long
Dim iCountVisible As Integer
Dim Cell As Range

' Select Colour in Cell J1 and put Integer value into lFilterColour
lFilterColour = Range("J1").Interior.ColorIndex
Call UnhideAll 'Call UnhideAll procedure to Unhide the previous filter.

Range("A1").Select 'Selects first cell in range

iCountVisible = 0 ' FilterByColor Counter

'Start of Loop, to filter each row in turn, based on value from Col A vs lFilterColour
Do

iCountVisible = iCountVisible + 1
ActiveCell.Offset(1, 0).Activate
With Selection
If (Selection.Interior.ColorIndex <> lFilterColour) Then
Selection.EntireRow.Hidden = True
iCountVisible = iCountVisible - 1

End If​
End With​

Loop Until IsEmpty(ActiveCell.Offset(1, 0)) ' Loops until next row is empty (cell in Col A).

Range("J1").Select
Range("N1").Value = iCountVisible​

End Sub
And the Unhide Sub
Sub UnhideAll()
' Written 24/03/2008 by MRdNk
' Part of FilterByColour
Rows("2:11").EntireRow.Hidden = False
Range("N1").Value = "=Count(A2:A9)"​

End Sub
Ps. Also includes a records count.

Tried to For Each Cell In ??? (got stuck here).
 
Joined
Jul 1, 2005
Messages
8,546
"a better way of doing it, please advise." & "Tried to For Each Cell In ??? (got stuck here)."

Certainly you should avoid physical selection of sheets/ranges/cells wherever possible, in terms of efficiency.

For the For/Next construct, something like:

For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
MsgBox Cell.Address' (i.e. your code to loop here)
Next Cell


HTH
 

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
That's great thank you.

I've used a named range with OFFSET and COUNTA for the height to improve it further.
Excellent.

My description, of what I was looking for was a bit lame, long day.
 
Joined
Jul 1, 2005
Messages
8,546
Something I regularly forget to remember ( :D ) is ... how many records are we talking?

Code execution can often be greatly sped up by wrapping the "bulk" between:

Application.ScreenUpdating=False

and

Application.ScreenUpdating=True

:cool:
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
I want to say it is built into 2007 as well. At least I know you can sort by color if I recall, but do not remember if you can filter.
 

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
Yeah, I'm pretty sure 2007 does have Filter by Colour which is a great addition. Unfortunately I'm still on 2003 as is the place I work - and I'm sure it'll be a while before its standard.
 

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
How would I do it, if the colour was determined by a conditional format?
 
Joined
Jul 1, 2005
Messages
8,546
AFAIK code can't see colours "created" by conditional formatting, therefore you can't. You'd have to have the code check for the same conditions that governed the CF.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top