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