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
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 |
#6
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 |
#7
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 |
#8
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 |
#9
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
See if this is any quicker. It utilises an approach proposed by Dave
Peterson Sub HideGrey() Dim cell As Range Dim rngIsect As Range Dim rngColoured As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set rngIsect = Application.Intersect(ActiveSheet. _ UsedRange, Range("A:A")) Columns(2).Insert For Each cell In rngIsect If cell.Interior.ColorIndex = 15 Then 'Gray-25% cell.Offset(0, 1).Value = 1 End If Next Rows(1).Insert Range("B1").Value = "Temp" Set rngColoured = Range("B1").Resize(Cells(Rows.Count, "B").End(xlUp).Row) rngColoured.AutoFilter field:=1, Criteria1:="1" Set rngColoured = rngColoured.SpecialCells(xlCellTypeVisible) rngColoured.AutoFilter rngColoured.EntireRow.Hidden = True Rows(1).Delete 'Columns(2).Delete Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub -- --- 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:6dcc96641ea30@uwe... 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
That is substantially quicker. Almost instantaneous, which is good enough
for me. Is there a reason that it didnt delete the column it also created? You have it as just a comment right. Can you explain what that is basically doing? im not totally following. thanks a lot Bob Phillips wrote: See if this is any quicker. It utilises an approach proposed by Dave Peterson Sub HideGrey() Dim cell As Range Dim rngIsect As Range Dim rngColoured As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set rngIsect = Application.Intersect(ActiveSheet. _ UsedRange, Range("A:A")) Columns(2).Insert For Each cell In rngIsect If cell.Interior.ColorIndex = 15 Then 'Gray-25% cell.Offset(0, 1).Value = 1 End If Next Rows(1).Insert Range("B1").Value = "Temp" Set rngColoured = Range("B1").Resize(Cells(Rows.Count, "B").End(xlUp).Row) rngColoured.AutoFilter field:=1, Criteria1:="1" Set rngColoured = rngColoured.SpecialCells(xlCellTypeVisible) rngColoured.AutoFilter rngColoured.EntireRow.Hidden = True Rows(1).Delete 'Columns(2).Delete Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Nope, still takes forever. all the other macros that occur on that page dont [quoted text clipped - 13 lines] 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hiding row base on cell content
Sorry, that comment was added at the last minute in my testing, I should
have removed it so that it did get deleted. As I said, it utilises the approach as suggested by Dave Peterson, but in VBA. - I insert a helper column, and then loop through putting a 1 in that column for every grey row. - it then filters on that helper column, filter the value of 1. - the matching cells are extracted into a range object using the SpecialCells method on visible cells - the filter is removed and the inserted row and column is deleted - the rows applying to the previously determined range is hidden -- --- 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:6dcd724151c2b@uwe... That is substantially quicker. Almost instantaneous, which is good enough for me. Is there a reason that it didnt delete the column it also created? You have it as just a comment right. Can you explain what that is basically doing? im not totally following. thanks a lot Bob Phillips wrote: See if this is any quicker. It utilises an approach proposed by Dave Peterson Sub HideGrey() Dim cell As Range Dim rngIsect As Range Dim rngColoured As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set rngIsect = Application.Intersect(ActiveSheet. _ UsedRange, Range("A:A")) Columns(2).Insert For Each cell In rngIsect If cell.Interior.ColorIndex = 15 Then 'Gray-25% cell.Offset(0, 1).Value = 1 End If Next Rows(1).Insert Range("B1").Value = "Temp" Set rngColoured = Range("B1").Resize(Cells(Rows.Count, "B").End(xlUp).Row) rngColoured.AutoFilter field:=1, Criteria1:="1" Set rngColoured = rngColoured.SpecialCells(xlCellTypeVisible) rngColoured.AutoFilter rngColoured.EntireRow.Hidden = True Rows(1).Delete 'Columns(2).Delete Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Nope, still takes forever. all the other macros that occur on that page dont [quoted text clipped - 13 lines] 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 |
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 |