Rick,
This post is almost surely way too long, but I think a full explanation is
in order.

I have just now realized how tough a job you guys have in answering our
questions because you are not mind readers and cannot really know our level
of knowledge. A friend of mine uses the acronym of COIC for this phenomenon.

COIC: Clear, Only If Known. (i.e. it's clear to me, 'cuz I'm writing it and
I know all the background, LOL) There are also times when my screwy brain
interprets what you guys say in the wrong way. I have a flaw(one among many)
where I take things way too literally. That said, .....

I'm combining your last two posts in this thread along some of the prior's.
They are not in strict chronological sequence.

Your offer to have me send you more complete examples is kind, but I don't
think I'll need to. I am self taught in VBA and decently knowledgeable in
some stuff, but the rawest of rookies in others.

This is the "problem" that kicked off this entire thread. The data was
rows 1 and 10 were hidden, 2-9 not hidden.

If Rows("1:10").Hidden = True Then ......

To me, it's just stupid, as Latham explained that the state of the first
row in the range is returned. (God help me, it 'should' be False since all of
the rows were not hidden, but I never knew, nor came across any reference
to the DIFFERENCE between TESTING a range, and USING a range to set all of
its values, properties, to "something".

So, I wrote the HidnQtyF function. It's objective is to return a count of
hidden rows (or columns) in the range specified in the arguments, as well as
returning the range of what's actually hidden so the calling proc can
unhide them, and as needed, re-hide them.
The reason for this is that there are sheets in my App that are
protected but cannot be password protected. (You'll have to take this on
faith for now) If a user hides stuff that "shouldn't" be hidden the code can
have a problem since I try to find stuff in rows or columns.

We argued a bit about whether or not .Find can find stuff in hidden cells.

Your words: "The Find function works fine locating text in hidden cells
in my tests." .... "irrespective of the hidden state of the cells being
searched."

This is what you were ALLUDING to (my emphasis) when you spoke
about .Find remembering the prior use's settings.

Yet, you said "Correct" to this example:
debug.print activesheet.cells.find("word",,xlvalues,xlpart).ad dress

The above failed when the word was in a hidden row. My brain works like
this:
- the value existed
- it was in a hidden row
- .Find did not find it using xlvalues
- .Find found it with xlformulas
- The hidden state is not "irrespective"
- If it's hidden, and you want to find it, unhide it or use xlFormulas.
- To me, this is not a .Find prior setting issue, it's a correct parm issue
but there's no way you could know that, and that I knew the difference
between xlValues and xlFormulas as optional parms in .Find.

Honest to gosh I'm not trying to prove anyone is "wrong", but your phrase

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

maybe coulda have had "If you look for it in the right way." at the end of
the phrase. We agree on the facts, we can agree to disagree on the
language (LOL, English not vba)


your comment on the ComposeColAdr: paragraph.

I wanted the posted HidnQtyF code to be self contained, so I rephrased
another function and turned it into the paragraph.

I think I know that the .Columns(????) vba syntax can be:
Set RangeName = Ws.Columns(Stuff)
Stuff can be: a column #, column letter(s), or "letter(s) & ":" &
letter(s)"
UNlike .Rows(Stuff)
it cannot be "1:10" for columns A:J, as I got a runtime error on that.

I use the function when the calling proc does not use column letter
variables, but only column number variables and I need the Stuff for
a contiguous column range. (When calling proc has the letters there's no
need to call the function.)

The complete function is at the end of this post. I'll try out your
.Resize usage to see if it's quicker than what's below.

Again, Many Thanks for your time on this,
Neal Z


Function ColRngAdrF(FMcol As Long, TOcol As Long, _
Optional ColRngId As String = "") As String
' Return an absolute column range address string via numeric from/to
'column numbers. Optional Id return of address without $.
'Invalid column numbers input defaults to minimum and maximum columns.
'lockdown,09/17/2008,
'renamed from ColRngStrF
'todo crit, need test MsoMaxCol Excel version, 256 versus ???

Dim Text As String

'mainline start
If FMcol < 1 Then
FMcol = 1
ElseIf FMcol MSoMaxCol Then
FMcol = MSoMaxCol
End If
If TOcol < 1 Then
TOcol = 1
ElseIf TOcol MSoMaxCol Then
TOcol = MSoMaxCol
End If

Text = Range(Cells(1, FMcol), Cells(1, TOcol)).Address 'get $E$1:$F$1
Text = Replace(Text, "$1", "") 'strip row out get $E:$F
ColRngAdrF = Text
ColRngId = Replace(Text, "$", "") 'strip $, get E:F
'mainline end
End Function
--
Neal Z


"Rick Rothstein" wrote:

I have looked over your code and I have a feeling it can be simplified
somewhat, but I am not sure of that because I don't fully understand the
ultimate purpose of the function. Can you show us some examples of various
worksheet setups along with the possible input conditions (function
arguments) and the expected outputs for them?

Just a quick observation on one section of your code. If I understand it
correctly, I believe this section...

ComposeColAdr: 'string address via two col #'s
RCAdr = Range(Cells(1, Col1), Cells(1, Col2)).Address 'f $E$1:$F$1
RCAdr = Replace(RCAdr, "$1", "") 'f $E:$F
Return

can be replaced with this...

ComposeColAdr: 'string address via two col #'s
RCAdr = Range(Columns(Col1), Columns(Col2)).Address 'f $E:$F
Return

which will work if Col1 and Col2 are either numbers or letters. Since you
have Dim'med Col1 and Col2 as Long, you can also use this alternative code
as well...

ComposeColAdr: 'string address via two col #'s
RCAdr = Columns(Col1).Resize(, Col2 - Col1 + 1).Address 'f $E:$F
Return

--
Rick (MVP - Excel)



"Neal Zimm" wrote in message
...
Hi J,
You were so helpful I thought you might like to see the almost finished
function. I'm going to test whether scanning a row range is quicker than
If rows(row).Hidden Then ...

BTW, the code works, I've not yet found any problems with it.

On an older computer with a 386 chip, 5000 rows with 10 of them hidden
took .17 seconds.
The code is self-contained, I've replaced some of my other function
calls
with GoSub's. (and hard coded MsoMaxRow and MSoMaxCol values.)

Thanks again.
Neal

Function HidnQtyF(ByVal Ws As Worksheet, bRowNum As Boolean, _
FMnumOrRng As Variant, TOnum As Long, Status As String, _
Optional bWantOutput As Boolean = False, _
Optional OutAyOrRng As Variant = "", _
Optional bExitFirHidn As Boolean = False, _
Optional INnumQty As Long = 0) As Long
'Outputs: Return count of hidden rows Xor columns, bRowNum input,
' True=scan for hidden rows, F= for columns.
' Optional array of hidden row/column numbers or a range bWantOutput
parm.
' If bWantOutput, the input OutAyOrRng parm type determines type of
output.
' An unallocated array() or a range object. Ay output is base 1.
' Neither raises error 13, type mismatch.
' INnumQty is the input count of rows or columns being scanned, see
bRowNum.
'
'Other Inputs: Ws sheet, if Nothing, Active is assumed.
' FMnumOrRng, a) Input range(Ws is set to .Parent) Areas.Count 1 is OK.
' b) From row or column #.
' c) Neither raises error 13 type mismatch.
' Tonum, the 'To' row/column # when FMnumOrRng is numeric. Lower to
Higher
' or vice-versa is OK for scanning row or column #'s.
' bExitFirHidn, False, scan all input. True, Proc quits after 1st hidden
row or
' col is found. Output has only first item. Function return value will
be
1.

'lockdown devel
Dim b1DimOut As Boolean
Dim bRangeIn As Boolean 'T= range input rather than FM and TO nums.

Dim RCAdr As String 'row(s) or column(s) string address

Dim Aix As Long 'area index
Dim Col1 As Long
Dim Col2 As Long
Dim FMnum As Long
Dim HiddenQty As Long
Dim InnerRC As Long 'inner loop row or column number
Dim MiscNum As Long 'miscell.
Const MSoMaxCol = 256 'todo, function, update values Excel 10,11,etc.
Const MSoMaxRow = 65536
Dim Qty As Long 'miscell.
Dim RC As Long 'row or column number
Dim ScanQty As Long 'row/col count in an area
Dim StepVal As Long 'up/down right/left row/col loops

'mainline start
Status = ""
INnumQty = 0

If IsNumeric(FMnumOrRng) Then 'f What's the input ?
If Ws Is Nothing Then Set Ws = ActiveSheet

ElseIf IsObject(FMnumOrRng) Then
If TypeName(FMnumOrRng) = "Nothing" Then
Status = "Warning, HidnQtyF, FMnumOrRng Input = Nothing"
Exit Function 'zip to scan
End If

If TypeName(FMnumOrRng) = "Range" Then
bRangeIn = True
Set Ws = FMnumOrRng.Parent
Else
Status = "Tech Error, HidnQtyF, FMnumOrRng Input Not Rng Obj"
Err.Raise 13
End If
Else
Status = "Tech Error, HidnQtyF, FMnumOrRng Input Not# Not Rng"
Err.Raise 13
End If

With Ws 'end with @ end sub

If Not bRangeIn Then '1 range, with from and to inputs.
FMnum = FMnumOrRng

If FMnum < 1 Then FMnum = 1
If TOnum < 1 Then TOnum = 1

If bRowNum Then
If FMnum MSoMaxRow Then FMnum = MSoMaxRow
If TOnum MSoMaxRow Then TOnum = MSoMaxRow
Else
If FMnum MSoMaxCol Then FMnum = MSoMaxCol
If TOnum MSoMaxCol Then TOnum = MSoMaxCol
End If

INnumQty = Abs(TOnum - FMnum) + 1
If FMnum <= TOnum Then StepVal = 1 Else StepVal = -1

If bWantOutput Then
ScanQty = INnumQty
GoSub AllocateOutP
End If

GoSub A_Ws_Scan 'f Main Loops

ElseIf bRowNum Then 'range input, 1 or more areas
For Aix = 1 To FMnumOrRng.Areas.Count
FMnum = FMnumOrRng.Areas(Aix).Row
TOnum = FMnum + FMnumOrRng.Areas(Aix).Rows.Count - 1
StepVal = 1
GoSub A_Ws_Scan
Next Aix
Else
For Aix = 1 To FMnumOrRng.Areas.Count
FMnum = FMnumOrRng.Areas(Aix).Column
TOnum = FMnum + FMnumOrRng.Areas(Aix).Columns.Count - 1
StepVal = 1
GoSub A_Ws_Scan
Next Aix
End If

If b1DimOut And HiddenQty 0 Then ReDim Preserve _
OutAyOrRng(1 To HiddenQty)
HidnQtyF = HiddenQty
'mainline end
Exit Function


A_Ws_Scan: 'Scan rows or columns, count, write outputs per function parms.

ScanQty = Abs(TOnum - FMnum) + 1
If bRangeIn Then INnumQty = INnumQty + ScanQty

If Not bWantOutput Then 'count only.
If bRowNum Then
For RC = FMnum To TOnum Step StepVal
If .Rows(RC).Hidden Then
HiddenQty = HiddenQty + 1
If bExitFirHidn Then Return
End If
Next RC
Else
For RC = FMnum To TOnum Step StepVal
If .Columns(RC).Hidden Then
HiddenQty = HiddenQty + 1
If bExitFirHidn Then Return
End If
Next RC
End If

ElseIf bRowNum Then 'Scan, write outputs, hidden rows
If bRangeIn Then GoSub AllocateOutP

For RC = FMnum To TOnum Step StepVal
If .Rows(RC).Hidden Then
If Not bExitFirHidn Then 'Scan all for hidden 'til input rows end.
If b1DimOut Then
HiddenQty = HiddenQty + 1
OutAyOrRng(HiddenQty) = RC 'f updated array

Else
InnerRC = RC
'f update range when contiguous hidden's end.
Do While .Rows(InnerRC + StepVal).Hidden And _
Abs(InnerRC + StepVal - FMnum + 1) <= ScanQty
InnerRC = InnerRC + StepVal
Loop

HiddenQty = HiddenQty + Abs(InnerRC - RC) + 1
RCAdr = RC & ":" & InnerRC
GoSub AddToOutRange

RC = InnerRC 'back to For/Next
End If 'updated range
Else
HiddenQty = 1
If b1DimOut Then
OutAyOrRng(1) = RC
Else
RCAdr = RC
GoSub AddToOutRange
End If
Return
End If 'f updated array or range
End If 'row is hidden
Next RC

Else 'Scan, write outputs, hidden columns
If bRangeIn Then GoSub AllocateOutP

For RC = FMnum To TOnum Step StepVal
If .Columns(RC).Hidden Then
If Not bExitFirHidn Then
If b1DimOut Then
HiddenQty = HiddenQty + 1
OutAyOrRng(HiddenQty) = RC

Else
InnerRC = RC

Do While .Columns(InnerRC + StepVal).Hidden And _
Abs(InnerRC + StepVal - FMnum + 1) <= ScanQty
InnerRC = InnerRC + StepVal
Loop

HiddenQty = HiddenQty + Abs(InnerRC - RC) + 1
'RCAdr = ColRngAdrF(RC, InnerRC) '$A:$B from col #'s
Col1 = RC
Col2 = InnerRC
GoSub ComposeColAdr
GoSub AddToOutRange

RC = InnerRC 'back to For/Next
End If 'updated range
Else
HiddenQty = 1
If b1DimOut Then
OutAyOrRng(1) = RC
Else
'RCAdr = ColRngAdrF(RC, RC)
Col1 = RC
Col2 = RC
GoSub ComposeColAdr
GoSub AddToOutRange
End If
Return
End If 'f updated array or range
End If 'row is hidden
Next RC
End If
Return


AddToOutRange: 'Set with Union or not
If bRowNum Then
If Not OutAyOrRng Is Nothing Then
Set OutAyOrRng = Union(OutAyOrRng, .Rows(RCAdr))
Else
Set OutAyOrRng = .Rows(RCAdr)
End If
Else
If Not OutAyOrRng Is Nothing Then
Set OutAyOrRng = Union(OutAyOrRng, .Columns(RCAdr))
Else
Set OutAyOrRng = .Columns(RCAdr)
End If
End If
Return

AllocateOutP: 'Dim/ReDim array to hold row or column #'s, Init Rng output.
If bRangeIn Then 'init on area 1, then redim array for +1 areas
If Aix = 1 Then
GoSub AllocateAy1st