Hi -
I built the FindRngData Sub below as a tool.

While running TestIt, I was able to find data in hidden rows, [or
columns], which was a surprise, after reading some of the postings here.

I could not find any talk finding xlValues versus xlFormulas, and,
the data I'm looking for in my App is almost always hand entered.

After running some iterations of TestIt, I've concluded that:
Values must be found in not hidden cells.
Data in the formulas property can be found regardless of .Hidden .

Do I have this right ?

What are other pitfalls in the "hidden arena" of which I'm not aware ?

Thanks,
Neal Z.


Sub TestIt()
Const TxnTypCol = 3
Dim AllRowsRng As Range
Dim TxnTypColRng As Range
Dim TxnTypRng As Range
Dim TxnTyp As String
Dim ChkBkFirRow As Long, ChkBkLasRow As Long
Dim lCount As Long

If ActiveSheet.Name < "test" Then
MsgBox "ONLY test sheet", vbCritical, "TestIt"
Exit Sub
End If

ChkBkFirRow = 9: ChkBkLasRow = 24

Set AllRowsRng = Rows(ChkBkFirRow & ":" & ChkBkLasRow)
AllRowsRng.Rows.Hidden = True

Set TxnTypColRng = Range(Cells(ChkBkFirRow, TxnTypCol), _
Cells(ChkBkLasRow, TxnTypCol))

TxnTyp = "Dep"

Call FindRngData(TxnTypColRng, TxnTyp, TxnTypRng, lCount, _
bFormulas:=True) 'False looks for values

If Not TxnTypRng Is Nothing Then TxnTypRng.Rows.Hidden = False
' formula stuff showed up, values did not.
End Sub


Sub FindRngData(InRng As Range, vFind, DupeRng As Range, lCount As Long, _
Optional bOneRng As Boolean = True, Optional Found1Rng As Range = Nothing, _
Optional bWhole As Boolean = True, Optional AfterRng As Range = Nothing, _
Optional bFormulas As Boolean = True, Optional iAreas As Integer = 0, _
Optional bDebugPrt As Boolean = False)
'Return data range containing vFind.
'Outputs: See also bOneRng input.
' Found1Rng, not nothing holds 1st find.
' DupeRng, not nothing holds ADDITIONAL cells.
' lCount = qty cells in DupeRng. iAreas = DupeRng area count.
'Inputs: InRng = search range, vFind = What to look for.
' bWhole, look in xlWhole, F = look in xlPart
' AfterRng, Nothing= proc values AfterRng as rightmost bottom cell
' so find starts AT top left cell, NOT MSo default.
' bFormulas, look in xlFormulas, F = xlValues
' bOneRng, DupeRng includes Found1Rng. lCount, iAreas are adjusted.
' bDebugPrt, T= print results

Dim Rng As Range
Dim sFirAdr As String
Dim LookAt As Integer
Dim LookIn As Integer

'mainline start
If InRng Is Nothing Then Exit Sub
If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub
Set Found1Rng = Nothing
Set DupeRng = Nothing
iAreas = 0
lCount = 0
If bWhole Then LookAt = xlWhole Else LookAt = xlPart
If bFormulas Then LookIn = xlFormulas Else LookIn = xlValues

With InRng
If AfterRng Is Nothing Then
Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _
(.Column + .Columns.Count - 1))
ElseIf AfterRng.Rows.Count < 1 Or _
AfterRng.Columns.Count < 1 Then
MsgBox "AfterRng is NOT 1 cell, " & AfterRng.Address, _
vbCritical, "Sub FindRngData"
End 'End.
End If

If IsNumeric(vFind) Then vFind = CDbl(vFind)

Set Rng = .Find(vFind, AfterRng, LookIn, LookAt)

If Not Rng Is Nothing Then
Set Found1Rng = Rng
sFirAdr = Found1Rng.Address
Do
Set Rng = .FindNext(Rng)

If Rng Is Nothing Or Rng.Address = sFirAdr Then
Exit Do
Else
lCount = lCount + 1
If lCount = 1 Then Set DupeRng = Rng _
Else Set DupeRng = Union(DupeRng, Rng)
End If
Loop While Not Rng Is Nothing
End If
End With

If Not Found1Rng Is Nothing And bOneRng Then
If DupeRng Is Nothing Then
Set DupeRng = Found1Rng
lCount = 1
Else
Set DupeRng = Union(Found1Rng, DupeRng)
lCount = lCount + 1
End If
End If

If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count

If bDebugPrt Then
Debug.Print Cr & "FindRngData " & Now & " InRng " & InRng.Address
If bFormulas Then Debug.Print "LookIn xlFormulas" _
Else Debug.Print "LookIn xlValues"
Debug.Print "vFind " & vFind & " OneRng " & bOneRng & " lCount "
& lCount
If Not Found1Rng Is Nothing Then
Debug.Print "First: " & Found1Rng.Address
If DupeRng Is Nothing Then Debug.Print "NO Dupes" _
Else Debug.Print "Dupes: " & DupeRng.Address
Else
Debug.Print "vFind Not Found"
End If
End If
'mainline end
End Sub

--
Neal Z