Hide and unhide shaded cells

  • Thread starter Thread starter Sylve
  • Start date Start date
S

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
 
Are you ready to embark on learning a little Visual Basic?
What version of Excel (2007 or before)?
best wishes
 
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
 
'Bob Umlas[_3_ said:
;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
Click to expand...


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 ?
 
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
 
Note: I can't remember if the Edit>Find by format will work in versions
earlier than 2003.


Gord
 
Gord said:
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
Click to expand...



Thank you God... I mean thank you Gord
The VBA code works like a charm !
I'm really greatful to Mr. Dibben. Thanks agai
 
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>
 
Back
Top