Hide and unhide shaded cells

[Sylve]

Hi everyone! New here and in Excel too. I want to hide shaded rows
(different colours) and see only unshaded rows. Later I will unhide
those rows.

Thanks in advance for your answers

PS. I search the forum but no luck

 

[Bernard Liengme]

Are you ready to embark on learning a little Visual Basic?
What version of Excel (2007 or before)?
best wishes

 

[Bob Umlas]

You can one time manually hide the ones you want, and then use View Manager
to give that view a name. Then, using View manager, it's a simple click to
return to that view of your workbook. You could also give a name to the view
of all rows being shown.
Bob Umlas
Excel MVP

 

[Sylve]

;806275']You can one time manually hide the ones you want, and then use
View Manager
to give that view a name. Then, using View manager, it's a simple clic
to
return to that view of your workbook. You could also give a name to th
view
of all rows being shown.
Bob Umlas
Excel MVP

Thank you Bob for your answer but I have a spreadsheet with aprox 400
rows... I think half of them are coloured rows. Manually hiding 2000
random rows?? Hard job to do.
Let me ask the same question in a different way: How I sort a workboo
by shaded/unshaded cells ?

 

[Gord Dibben]

For any one color............

Edit>Find>Format>Format....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar Format>Row>Hide

If you have many colors to do you may want to use VBA instead.

Sub hide()
Dim rng1 As Range
Set rng1 = Range(Cells(, 1), Cells(Rows.Count, _
Cells(, 1).Column).End(xlUp))
For Each cell In rng1
If cell.Interior.ColorIndex <> xlNone Then
cell.EntireRow.Hidden = True
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Note: I can't remember if the Edit>Find by format will work in versions
earlier than 2003.


Gord

 

[Sylve]

For any one color............

Edit>Find>Format>Format....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar Format>Row>Hide

If you have many colors to do you may want to use VBA instead.

Sub hide()
Dim rng1 As Range
Set rng1 = Range(Cells(, 1), Cells(Rows.Count, _
Cells(, 1).Column).End(xlUp))
For Each cell In rng1
If cell.Interior.ColorIndex <> xlNone Then
cell.EntireRow.Hidden = True
End If
Next
End Sub


Gord Dibben MS Excel MVP

Thank you God... I mean thank you Gord
The VBA code works like a charm !
I'm really greatful to Mr. Dibben. Thanks agai

 

[Dave Peterson]

That was one of the enhancements that came with xl2002.

 

[Gord Dibben]

Thanks Dave


Gord

That was one of the enhancements that came with xl2002.

 

[Gord Dibben]

Thanks for the feedback.

More religious references<g>

That's for Dave


Gord

 

[Dave Peterson]

Well, it is Sunday (where I'm sitting!)

Thanks for the feedback.

More religious references<g>

That's for Dave

Gord

 

[Gord Dibben]

Sunday here also.

With my newly elevated position I could re-write that 11th chapter in 2nd
Samuel and paint King David with a different brush............if you wish<g>

 

[Dave Peterson]

It would be tough to rewrite that to make that guy likeable. I can see why
you'd be upset!

http://www.biblegateway.com:80/passage/?book_id=10&chapter=11&version=31