Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 4
Default Hide and unhide shaded cells [RESOLVED]

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

Last edited by Sylve : March 8th 09 at 11:30 AM
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,104
Default Hide and unhide shaded cells

Are you ready to embark on learning a little Visual Basic?
What version of Excel (2007 or before)?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Sylve" wrote in message
...

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




--
Sylve



  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 320
Default Hide and unhide shaded cells

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" wrote in message
...

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




--
Sylve



  #4   Report Post  
Junior Member
 
Posts: 4
Default

Quote:
Originally Posted by Bob Umlas[_3_] View Post
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

Thank you Bob for your answer but I have a spreadsheet with aprox 4000 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 workbook by shaded/unshaded cells ?
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default Hide and unhide shaded cells

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

EditFindFormatFormat....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar FormatRowHide

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


On Sat, 7 Mar 2009 18:28:15 +0000, Sylve
wrote:

Thank you Bob for your answer but I have a spreadsheet with aprox 4000
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 workbook
by shaded/unshaded cells ?




  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default Hide and unhide shaded cells

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


Gord

On Sat, 07 Mar 2009 15:38:58 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

EditFindFormatFormat....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar FormatRowHide

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


On Sat, 7 Mar 2009 18:28:15 +0000, Sylve
wrote:

Thank you Bob for your answer but I have a spreadsheet with aprox 4000
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 workbook
by shaded/unshaded cells ?


  #7   Report Post  
Junior Member
 
Posts: 4
Default

Quote:
Originally Posted by Gord Dibben View Post
For any one color............

EditFindFormatFormat....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar FormatRowHide

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 again
  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Hide and unhide shaded cells

That was one of the enhancements that came with xl2002.

Gord Dibben wrote:

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

Gord

On Sat, 07 Mar 2009 15:38:58 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

EditFindFormatFormat....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar FormatRowHide

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


On Sat, 7 Mar 2009 18:28:15 +0000, Sylve
wrote:

Thank you Bob for your answer but I have a spreadsheet with aprox 4000
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 workbook
by shaded/unshaded cells ?


--

Dave Peterson
  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default Hide and unhide shaded cells

Thanks Dave


Gord

On Sun, 08 Mar 2009 10:47:01 -0500, Dave Peterson
wrote:

That was one of the enhancements that came with xl2002.

Gord Dibben wrote:

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

Gord

On Sat, 07 Mar 2009 15:38:58 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

EditFindFormatFormat....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar FormatRowHide

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


On Sat, 7 Mar 2009 18:28:15 +0000, Sylve
wrote:

Thank you Bob for your answer but I have a spreadsheet with aprox 4000
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 workbook
by shaded/unshaded cells ?


  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default Hide and unhide shaded cells

Thanks for the feedback.

More religious references<g

That's for Dave


Gord

On Sun, 8 Mar 2009 10:15:32 +0000, Sylve
wrote:


Gord Dibben;806747 Wrote:
For any one color............

EditFindFormatFormat....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar FormatRowHide

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 again




  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Hide and unhide shaded cells

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

Gord Dibben wrote:

Thanks for the feedback.

More religious references<g

That's for Dave

Gord

On Sun, 8 Mar 2009 10:15:32 +0000, Sylve
wrote:


Gord Dibben;806747 Wrote:
For any one color............

EditFindFormatFormat....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar FormatRowHide

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 again


--

Dave Peterson
  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default Hide and unhide shaded cells

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




On Sun, 08 Mar 2009 15:25:59 -0500, Dave Peterson
wrote:

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

Gord Dibben wrote:

Thanks for the feedback.

More religious references<g

That's for Dave

Gord

On Sun, 8 Mar 2009 10:15:32 +0000, Sylve
wrote:


Gord Dibben;806747 Wrote:
For any one color............

EditFindFormatFormat....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar FormatRowHide

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 again


  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Hide and unhide shaded cells

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/passa...=11&version=31

Gord Dibben wrote:

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

On Sun, 08 Mar 2009 15:25:59 -0500, Dave Peterson
wrote:

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

Gord Dibben wrote:

Thanks for the feedback.

More religious references<g

That's for Dave

Gord

On Sun, 8 Mar 2009 10:15:32 +0000, Sylve
wrote:


Gord Dibben;806747 Wrote:
For any one color............

EditFindFormatFormat....choose a color and OK find all.

CTRL + a to select all "found" items.

On menu bar FormatRowHide

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 again


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shaded cells won't print shaded Linda C Excel Worksheet Functions 4 April 3rd 23 06:59 PM
Hide text in shaded cell mmb Excel Worksheet Functions 3 November 3rd 06 05:45 PM
Switching views - Hide / Unhide preselected cells (Repost) Turquoise_dax Excel Discussion (Misc queries) 0 June 15th 06 07:03 PM
Hide Unhide Colin Excel Discussion (Misc queries) 4 April 9th 06 05:01 PM
hide/unhide cells garpavco Excel Worksheet Functions 3 January 13th 05 02:33 AM


All times are GMT +1. The time now is 05:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"