IF however, the .Value < .Formula property for the cell, .Find will not
find it if the cell is hidden. At least my tests prove this. (I've had a
pretty long thread on this board with others who explained it to me.)

I am probably misunderstanding what the actual problem is, but to the best
of my knowledge and experience, Find can find text in a cell whether that
text is displayed in the cell as a constant or as the result of a formula OR
if that text is inside the formula itself and whether the cell is hidden or
not does not matter. If you have an example where this is not true, then if
you would please send it to me (remove the NO.SPAM stuff from my address)
with a brief description of what is not being found and where it is located
at, then I would be happy to look at it.

--
Rick (MVP - Excel)



"Neal Zimm" wrote in message
...
Hi Rick,
Thanks for the post, but respectfully, I disagree with your statement
below with to me, an important qualification.

"The Find function works fine locating text in hidden cells in my tests."

I agree, IF the text does NOT result from a formula. If the text is a
constant so that .Value is the same as .Formula, .Find works great.

IF however, the .Value < .Formula property for the cell, .Find will not
find it if the cell is hidden. At least my tests prove this. (I've had a
pretty long thread on this board with others who explained it to me.)

The reason for my writing a hidden row or column function that kicked off
this thread, is that I have several utility procs that use .Find in them.

If I'm looking for .Value, and "I" don't know if rows are columns are
hidden, I want the capability of un-hiding the rows and columns before
executing the .Find.

I've put all of the code for my HidnQtyF function in a reply to JLatham
in this thread. I'm completing the testing for it, but so far so good. If
you'd like to give it a spin, I'd appreciate any comments.

Any yes, I agree it's a pain in the ass that .Find remembers the prior
settings, But I can see MSoft's point of view when using Cntl + F to find
the
next values.
I don't know a whole lot about custom dialog boxes, but there "oughta be
a
way" for it to provide the attributes of a find, but have the code reset
to
the defaults each time it executes.

Thanks for the post, I appreciate very much all of the time you guys
spend
on this board,

Regards,
Neal


--
Neal Z


"Rick Rothstein" wrote:

The Find function works fine locating text in hidden cells in my tests.
Maybe you are getting tripped up by the settings used on previous
searches
you did. Find has an annoying (well, at least to me) feature whereby it
remembers the settings from the last time it was used. So, if you set it
to
look at the whole cell's content and now you are trying to find a word
within the text of a cell, it won't find it because the setting from last
time told it to look at the whole word. It is always a good idea to
specify
the optional arguments to the Find function every time you use it.

--
Rick (MVP - Excel)



"Neal Zimm" wrote in message
...
J -
There's one last question this thread @ then end of this post.

Thanks. Further testing after I made the post revealed to me that
it's
a
'first row' in the range problem, I had thought it might be about only
row
1.

The function I'm building is part of a .Find 'utility' function since
.Find won't find values in cells that are hidden, so I need to test for
"hiddens" and un-hide them before the .Find. and then re-hide them as
needed.

I'll incorporate your ideas with one addition. The size of the row
range
I have to check is about 3000 rows and in the code I didn't show
there's
an
Areas.Count layer since there may be non-contiguous groups of hidden
rows.

Would you say 3000 rows is on the small or medium size ? (Excel 2007
changed the max row game)

Last question: you can save me some testing time
Is it more efficient, i.e. less execution time, to use

For Each "Something" in ARange
more code here
Next "Something"

versus, (when it comes to entire rows or columns)

For Row = Fromrow To ToRow Step StepVariable
If Rows(Row).Hidden = whatever then ....
Next Row

My reason for the row loop is it gives me the flexibility of
going
from 1 to 10 or from 10 to 1 by varying the StepVariable. I have not
figured
out a way to make For Each go backwards within 1 Area. You're kinda
stuck
with upper left to lower right, huh ?

Since I'm building a Hidden "utility" proc, I wanted forwards and
backwards capability.

Thanks again,
Neal Z. ]





--
Neal Z


"JLatham" wrote:

Second question First: the test of .Hidden for a range that includes
several
rows will return the state of the first row in the range. With a
range
that
includes rows 1:10 and row 1 hidden, then it would return True. But
if
row 1
was visible and even all 9 other rows were hidden, it would return
False.

So that results in the answer to your first question being pretty my
"Yes"
you have to test each one individually as far as I know. For the best
performance, especially on a very large number of rows, you should use
Range
objects to test. Some Examples:

We will assume that two variables are set up to hold the row numbers
involved: firstRow and lastRow

Dim testRows As Range
Dim anyRow As Range
Dim allHiddenFlag As Boolean
Set testRows = Rows(firstRow & ":" & lastRow)
'set default to assume all are hidden
allHiddenFlag = True
For Each anyRow In testRows
If anyRow.Hidden = False Then
allHiddenFlag=False
Exit For
End If
Next

'this setup would return the row number of the first hidden row
'in the range
Dim testRows As Range
Dim anyRow As Range
Dim firstHiddenRow As Long
Set testRows = Rows(firstRow & ":" & lastRow)
'set default to assume all are hidden
firstHiddenRow = 0
For Each anyRow In testRows
If anyRow.Hidden = trueThen
firstHiddenRow = anyRow.Row
Exit For
End If
Next
'test firstHiddenRow for non-zero

"Neal Zimm" wrote:

Hi All,
2 Questions follow,

This seems simple, but I'm not seeing it.
Below #2 is an excerpt from a bigger function.

1. Is there a quicker way to find rows that are hidden
within a larger row range, or is this about the
only way?
for row = x to y
if rows(row).hidden then .....
next row

I looked at .Find with the formats it can find but
.Hidden is a property, so , no go.


2. I do not understand why the If stmt below evaluated true
when all of rows 1 thru 10, inclusive, were not hidden.

Function Rows_HiddenQtyFV2(Ws As Worksheet, FMrow As Long, _
TOrow As Long, Optional bWantOutput As Boolean = False, _
Optional OutAyOrRng As Variant = "", _
Optional bExit1stHidn As Boolean = False, _
Optional INrowsQty As Long = 0) As Long


With Ws

INrowsQty = Abs(TOrow - FMrow) + 1

'FMrow was 10, TOrow was 1
' In the sheet rows 1 AND 10 were hidden, 2-9 not hidden.

If .Rows(FMrow & ":" & TOrow).Hidden = True Then
'more code here,
'don't all of them have to be hidden for "True"
End if

End With

'more code here to value the function
End Function

Thanks
--
Neal Z

.