Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
I did a search and came up with some things, but nothing quite like i want.
I want to have a macro linked to a button that when clicked will hide all rows that have a gray background in column A of that row. And then i will have another button that will unhide all those rows. Thanks, -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200702/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
Sub HideRows()
Dim cell As Range For Each cell In Selection cell.EntireRow.Hidden = cell.Offset(0, 1 - cell.Column).Interior.ColorIndex = 15 ' Gray-25% Next cell End Sub You should be able to figure the other one yourself. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "brownti via OfficeKB.com" <u31540@uwe wrote in message news:6dcac80c7b5d9@uwe... I did a search and came up with some things, but nothing quite like i want. I want to have a macro linked to a button that when clicked will hide all rows that have a gray background in column A of that row. And then i will have another button that will unhide all those rows. Thanks, -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200702/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
Try:
Sub HideRow() For Each c In Range("A:A") If c.Interior.ColorIndex = 15 Then 'Light grey c.EntireRow.Hidden = True End If Next c End Sub Sub UnHideRow() For Each c In Range("A:A") If c.EntireRow.Hidden = True Then c.EntireRow.Hidden = False End If Next c End Sub HTH "brownti via OfficeKB.com" wrote: I did a search and came up with some things, but nothing quite like i want. I want to have a macro linked to a button that when clicked will hide all rows that have a gray background in column A of that row. And then i will have another button that will unhide all those rows. Thanks, -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200702/1 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
Sub HideGrey()
Dim cell As Range Dim rngisect As Range Set rngisect = Application.Intersect(ActiveSheet. _ UsedRange, Range("A:A")) For Each cell In rngisect If cell.Interior.ColorIndex = 15 Then 'Gray-25% cell.EntireRow.Hidden = True End If Next End Sub --- HTH Jason Atlanta, GA "brownti via OfficeKB.com" wrote: I did a search and came up with some things, but nothing quite like i want. I want to have a macro linked to a button that when clicked will hide all rows that have a gray background in column A of that row. And then i will have another button that will unhide all those rows. Thanks, -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200702/1 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
This works except it doesnt stop once it has checked the sheet once. it just
continues checking... Jason Morin wrote: Sub HideGrey() Dim cell As Range Dim rngisect As Range Set rngisect = Application.Intersect(ActiveSheet. _ UsedRange, Range("A:A")) For Each cell In rngisect If cell.Interior.ColorIndex = 15 Then 'Gray-25% cell.EntireRow.Hidden = True End If Next End Sub --- HTH Jason Atlanta, GA I did a search and came up with some things, but nothing quite like i want. I want to have a macro linked to a button that when clicked will hide all rows that have a gray background in column A of that row. And then i will have another button that will unhide all those rows. Thanks, -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200702/1 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
I guess it does stop, but it takes years to perform. i changed it to not
screen update and it still takes forever. Any ideas to speed it up? brownti wrote: This works except it doesnt stop once it has checked the sheet once. it just continues checking... Sub HideGrey() [quoted text clipped - 21 lines] rows that have a gray background in column A of that row. And then i will have another button that will unhide all those rows. Thanks, -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
Maybe turn off automatic calculation
Application.Calculation = xlCalculationManual at the start, and back on at the end Application.Calculation = xlCalculationAutomatic -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "brownti via OfficeKB.com" <u31540@uwe wrote in message news:6dcc61eb87ed0@uwe... I guess it does stop, but it takes years to perform. i changed it to not screen update and it still takes forever. Any ideas to speed it up? brownti wrote: This works except it doesnt stop once it has checked the sheet once. it just continues checking... Sub HideGrey() [quoted text clipped - 21 lines] rows that have a gray background in column A of that row. And then i will have another button that will unhide all those rows. Thanks, -- Message posted via http://www.officekb.com |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
Nope, still takes forever. all the other macros that occur on that page dont
take nearly as long. could it just be the size of the sheet? 4000 rows? Bob Phillips wrote: Maybe turn off automatic calculation Application.Calculation = xlCalculationManual at the start, and back on at the end Application.Calculation = xlCalculationAutomatic I guess it does stop, but it takes years to perform. i changed it to not screen update and it still takes forever. Any ideas to speed it up? [quoted text clipped - 9 lines] will have another button that will unhide all those rows. Thanks, -- Message posted via http://www.officekb.com |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
How about another alternative.
Instead of basing the rules on the shade of the background, use a helper column and put some sort of indicator in it. Then apply data|filter|autofilter to your range--including that column and filter to show/hide the values you want shown/hidden. You may find that using the indicator column is more work, but that you can do more things with it later--without using macros. "brownti via OfficeKB.com" wrote: I did a search and came up with some things, but nothing quite like i want. I want to have a macro linked to a button that when clicked will hide all rows that have a gray background in column A of that row. And then i will have another button that will unhide all those rows. Thanks, -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200702/1 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cell Reference in a macro on global base | Excel Discussion (Misc queries) | |||
hiding content when there is a #REF in a cell | Excel Discussion (Misc queries) | |||
how to show different photos, base on different value of a cell? | Excel Worksheet Functions | |||
unique values in a cell base on another cell | Excel Discussion (Misc queries) | |||
Sumif and base it on font color of cell | Excel Worksheet Functions |