Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value in hidden cell
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value in hidden cell
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 "Greg Glynn" wrote: Stew, How are you hiding a Cell? Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value in hidden cell
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value in hidden cell
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value in hidden cell
Try looking in xlformulas instead of xlvalues.
stewdizzle wrote: 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? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find out if a row is hidden | Excel Worksheet Functions | |||
How to find non-blank cell values "hidden" under merged regions? | Excel Discussion (Misc queries) | |||
how to find if an excel sheet has any hidden values in a cell in VB.NET | Excel Programming | |||
find out if cell is hidden in if statement | Excel Programming | |||
Find Last Row Hidden | Excel Programming |