changing find to for/next

[ceemo]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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?

[broro183]

hi Ceemo,

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

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

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

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

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

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Bob Phillips]

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

--
HTH

Bob Phillips

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

"ceemo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[ceemo]

wow thats exactly whats i was after thank you very much

[broro183]

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

[ceemo]

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

[Bernie Deitrick]

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

[ceemo]

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)

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>