Testing/Finding .Hidden for Rows May21

[Neal Zimm]

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

 

[JLatham]

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]

J -
There's one last question re: 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 re:
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. ]

 

[Rick Rothstein]

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)

J -
There's one last question re: 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 re:
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

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]

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, re: bRowNum input,
' True=scan for hidden rows, F= for columns.
' Optional array of hidden row/column numbers or a range re: 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 re: 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
ElseIf b1DimOut Then 'Be able to hold all items about to be scanned.
MiscNum = UBound(OutAyOrRng) - HiddenQty 'available elements
If ScanQty > MiscNum Then
ReDim Preserve OutAyOrRng(1 To (UBound(OutAyOrRng) _
+ (ScanQty - MiscNum)))
End If
End If
Else 'one range of row or column #'s
GoSub AllocateAy1st
End If
Return

AllocateAy1st: 'First initialization
If IsObject(OutAyOrRng) And (TypeName(OutAyOrRng) = "Nothing" Or _
TypeName(OutAyOrRng) = "Range") Then
Set OutAyOrRng = Nothing

Else 'f Erase when more than 1 dimen, re-dim
If Not IsArray(OutAyOrRng) Then
Status = "Tech Error, HidnQtyF, Input OutAyOrRng, Not Rng Not Ay"
Err.Raise 13
End If

MiscNum = 0
Do
MiscNum = MiscNum + 1
On Error Resume Next
Qty = UBound(OutAyOrRng, MiscNum)
Loop Until Err.Number <> 0
On Error GoTo 0
Qty = Qty - 1 'dimen of Ay

If 1 < Qty Then Erase OutAyOrRng
b1DimOut = True
ReDim OutAyOrRng(1 To ScanQty)
End If
Return

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
End With 'With Ws at main top
End Function

 

[Neal Zimm]

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

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)

J -
There's one last question re: 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 re:
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

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

:

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

.

 

[Rick Rothstein]

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)

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

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)

J -
There's one last question re: 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 re:
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


:

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

:

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

Rick,
Dammit, I was wrong, but I was right too.
.Find will not find a value in a hidden cell even if it's a constant.
I put "word" in a cell and hid the row.

from the immediate window

debug.print activesheet.cells.find("word",,xlvalues,xlpart).address

you'll get a 91 error as the cell was not found, so it can't print the
address

now try

debug.print activesheet.cells.find("word",,xlformulas,xlpart).address

and you'll get the cell address.

Neal Z.


--
Neal Z

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)

J -
There's one last question re: 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 re:
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

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

:

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

.

 

[Rick Rothstein]

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

Correct... this is what I was alluding to when I said "It is always a good
idea to specify the optional arguments to the Find function every time you
use it."... the settings for each argument guide the Find function as to
what it will find irrespective of the hidden state of the cells being
searched.

--
Rick (MVP - Excel)

Rick,
Dammit, I was wrong, but I was right too.
.Find will not find a value in a hidden cell even if it's a constant.
I put "word" in a cell and hid the row.

from the immediate window

debug.print activesheet.cells.find("word",,xlvalues,xlpart).address

you'll get a 91 error as the cell was not found, so it can't print the
address

now try

debug.print activesheet.cells.find("word",,xlformulas,xlpart).address

and you'll get the cell address.

Neal Z.


--
Neal Z

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)

J -
There's one last question re: 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 re:
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


:

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

:

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

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

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)


Re: 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 re: MsoMaxCol re: 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