I have the below code which rins really slow and i think it would be
better as a for range = 1 to 500 and then using next

Please could someone help me adapt it to do just that

Basically it looks in column a between rows 1 to 500 and for each cell
that has a y value it hides that row



Sub AutoHidePlanRows()

Let Chk = "Y"

With Worksheets("Plan").Range("a1:a500")
Set c = .Find(Chk, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'Hide cell here
Let MyAdd = c.Row
LetMyRow = MyAdd & ":" & MyAdd
Range(LetMyRow).Select
Selection.EntireRow.Hidden = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=545451

hi Ceemo,

I think a better approach would be to use an autofilter and filter fo
all rows that do not equal "y", the code only takes one line & use
Excel's inbuilt functionality which is often faster than other macr
approaches eg:

Selection.AutoFilter Field:=1, Criteria1:="<y"

where the one is the # of the column relative to the left of the are
that is being autofiltered.

btw, I think Ron Debruin has a good page on filtering for values - i
may pay to google it.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

--
broro18
-----------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...fo&userid=3006
View this thread: http://www.excelforum.com/showthread.php?threadid=54545

I don't think your proposed solution would be quicker, in fact I would
expect it to be slower.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"ceemo" wrote in
message ...

I have the below code which rins really slow and i think it would be
better as a for range = 1 to 500 and then using next

Please could someone help me adapt it to do just that

Basically it looks in column a between rows 1 to 500 and for each cell
that has a y value it hides that row



Sub AutoHidePlanRows()

Let Chk = "Y"

With Worksheets("Plan").Range("a1:a500")
Set c = .Find(Chk, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'Hide cell here
Let MyAdd = c.Row
LetMyRow = MyAdd & ":" & MyAdd
Range(LetMyRow).Select
Selection.EntireRow.Hidden = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


--
ceemo
------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=545451

Ceemo,

Try the macro below - it should be quicker.

HTH,
Bernie
MS Excel MVP

Sub CeemoHide()
Dim myR As Range
Dim myV As Range

Set myR = Range("A2", Range("A65536").End(xlUp))
myR.AutoFilter Field:=1, Criteria1:="Y"
Set myV = myR.SpecialCells(xlCellTypeVisible)
myR.AutoFilter
myV.EntireRow.Hidden = True
End Sub


"ceemo" wrote in message
...

I have the below code which rins really slow and i think it would be
better as a for range = 1 to 500 and then using next

Please could someone help me adapt it to do just that

Basically it looks in column a between rows 1 to 500 and for each cell
that has a y value it hides that row



Sub AutoHidePlanRows()

Let Chk = "Y"

With Worksheets("Plan").Range("a1:a500")
Set c = .Find(Chk, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'Hide cell here
Let MyAdd = c.Row
LetMyRow = MyAdd & ":" & MyAdd
Range(LetMyRow).Select
Selection.EntireRow.Hidden = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=545451