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

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

ah well is there anyway i can speed it up?


i supose the quickets way is to select all the cells in one go before
selecting hide but i dont know how to do this.

Any ideas as im currently having to do tis manually?


--
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

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

Autofilter seems best. See Bernie's response for an example.

--
HTH

Bob Phillips

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

"ceemo" wrote in
message ...

ah well is there anyway i can speed it up?


i supose the quickets way is to select all the cells in one go before
selecting hide but i dont know how to do this.

Any ideas as im currently having to do tis manually?


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

wow thats exactly whats i was after thank you very much


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

thanks for the feedback - pleased we could help.

Bernie, I like it!
I use the autofilter approach but up until now I've just autofiltered,
selected the visible cells & then worked with "selection" ...
Now that I've seen the concept of
"Set myV = myR.SpecialCells(xlCellTypeVisible)"
I'll be making use of this in my work esp. where I refer to the range
throughout the course of a macro.

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


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

If i wanted to change this to columns how could i do so.

Ive tried the below but it just produces an error


Sub AutoHidePlanRows()



Dim myR As Range

Dim myV As Range



Set myR = Range("A1", Range("A65536").End(xlUp))

myR.AutoFilter Field:=1, Criteria1:="Y"

Set myV = myR.SpecialCells(xlCellTypeVisible)

myR.AutoFilter

myV.EntireRow.Hidden = True





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,

You can't delete the first row, so change the A1 back to A2.

Not sure what you mean by " If i wanted to change this to columns how could i do so.".... Explain?

HTH,
Bernie
MS Excel MVP

If i wanted to change this to columns how could i do so.

Ive tried the below but it just produces an error


Sub AutoHidePlanRows()



Dim myR As Range

Dim myV As Range



Set myR = Range("A1", Range("A65536").End(xlUp))

myR.AutoFilter Field:=1, Criteria1:="Y"

Set myV = myR.SpecialCells(xlCellTypeVisible)

myR.AutoFilter

myV.EntireRow.Hidden = True





End Sub

the original code hides those rows where there is a y in column a. What
id like to do is hide those columns where there is a t in row 1
(oppisite if you like)


--
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. Note that as written, it is case insensitive. Chang ethe False to True to
make it match case as well.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myR As Range
Dim myC As Range
Dim myA As String

Set myR = Range("1:1")

Set myC = myR.Find(What:="t", LookAt:=xlWhole, MatchCase:=False)
myA = myC.Address

While Not myC Is Nothing
myC.EntireColumn.Hidden = True
Set myC = myR.FindNext(myC)
If myC.Address = myA Then GoTo FoundAll
Wend

FoundAll:

End Sub

"ceemo" wrote in message
...

the original code hides those rows where there is a y in column a. What
id like to do is hide those columns where there is a t in row 1
(oppisite if you like)


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