Hiding row base on cell content

[brownti via OfficeKB.com]

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,

 

[Bob Phillips]

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)

 

[Toppers]

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

 

[Jason Morin]

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

 

[Dave Peterson]

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]

This works except it doesnt stop once it has checked the sheet once. it just
continues checking...

 

[brownti via OfficeKB.com]

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?

This works except it doesnt stop once it has checked the sheet once. it just
continues checking...

Sub HideGrey()

[quoted text clipped - 21 lines]

 

[Bob Phillips]

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)

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?

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,

 

[brownti via OfficeKB.com]

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?

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]

 

[Bob Phillips]

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)

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?

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,

 

[brownti via OfficeKB.com]

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

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]

 

[Bob Phillips]

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)

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

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,