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]

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

 

[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

 

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

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

 

[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