Find value in hidden cell

[stewdizzle]

My excel sheet has a list of names with info corresponding to the name
in the same row. I am constantly adding and deleting names and hiding
and unhiding rows. I decided to write a code that will search through
the list of names. Find the first "available" row and make it
visible. The part I am having trouble with right now is hiding and
unhiding. Here is my code:

Sub Findnshow
with worksheets(2).range("a4:a15")
'look for blank cell
set c = .find("", lookin:=xlvalues)
if not c is nothing then
firstaddress = c.address
select blank cell
c.select
end if
end with
hide entire row
selection.entirerow.hidden=true
end sub

If i try to unhide the row it does the search but does noot look at
the hidden cell. It skips to the next available cell and since it is
not hidden it does nothing. How can I unhide it?

 

[Greg Glynn]

Stew,

How are you hiding a Cell?

Greg

 

[OssieMac]

I think that I can see what you are trying to do so try this code and let me
know if it works for you. As a side issue, you should not code the Find
without all of its arguments because it always uses the last ones set even if
that was done in the interactive mode. Look up Find Method in VBA Help
(Answer Wizard tab) and click on show all and you will find this under the
Remarks.
Sub Findnshow()

Dim cell1
Dim Rng1 As Range

Set Rng1 = Worksheets("TestAutoFilt").Range("A4:A15")
'Rng1.EntireRow.Hidden = False

Set c = Rng1.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
InitAddress = c.Address 'Save the first address
Do 'Look for further occurrences of the find criteria
c.EntireRow.Hidden = False
Set c = Rng1.FindNext(c)
Loop While Not c Is Nothing And c.Address <> InitAddress
End If

End Sub

 

[OssieMac]

I left the following line in the code which I was using during testing and
had commented out so don't use it.
( 'Rng1.EntireRow.Hidden = False)

Also edit the Worksheets("TestAutoFilt").Range("A4:A15") line and insert
your sheet number.

 

[OssieMac]

Another example of hiding and unhiding rows depending on the contents of a
cell in the range. If what I have given you already is not what you are after
then this might give you enough info to do what you want to do.

Sub Hide_Unhide_Rows()
'Unhides first row without data
'Hide all other rows without data
'Unhides all rows with data

Dim Rng1 As Range
Dim BlnkId

Set Rng1 = Worksheets(2).Range("A2:A15")

BlnkId = False
For Each c In Rng1
Select Case c
Case ""
'Unhide first row with a blank cell
If BlnkId = False Then 'No previous occurrences
'Will only perform this action once
c.EntireRow.Hidden = False 'Unhide row
BlnkId = True 'Prevents action being repeated
Else
'Hide all other rows with blank cell
c.EntireRow.Hidden = True 'Hide Row
End If

Case Is <> ""
'Unhide all other rows with data
c.EntireRow.Hidden = False
End Select
Next c
End Sub

 

[Dave Peterson]

Try looking in xlformulas instead of xlvalues.

My excel sheet has a list of names with info corresponding to the name
in the same row. I am constantly adding and deleting names and hiding
and unhiding rows. I decided to write a code that will search through
the list of names. Find the first "available" row and make it
visible. The part I am having trouble with right now is hiding and
unhiding. Here is my code:

Sub Findnshow
with worksheets(2).range("a4:a15")
'look for blank cell
set c = .find("", lookin:=xlvalues)
if not c is nothing then
firstaddress = c.address
select blank cell
c.select
end if
end with
hide entire row
selection.entirerow.hidden=true
end sub

If i try to unhide the row it does the search but does noot look at
the hidden cell. It skips to the next available cell and since it is
not hidden it does nothing. How can I unhide it?