Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
|
|||
|
|||
Quote:
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
|
|||
|
|||
Quote:
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shaded cells won't print shaded | Excel Worksheet Functions | |||
Hide text in shaded cell | Excel Worksheet Functions | |||
Switching views - Hide / Unhide preselected cells (Repost) | Excel Discussion (Misc queries) | |||
Hide Unhide | Excel Discussion (Misc queries) | |||
hide/unhide cells | Excel Worksheet Functions |