Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding/Unhiding rows
I have a button that when the user presses the button the program will search
an ordered column for values equal to 0 and hide the rows that has values equal to zero. If the rows already are hidden then they shall be revealed by pressing the button. My code is: Sub showHideButton_Klicka() Dim relativCell As Range Dim i As Integer, j As Integer, k As Integer Dim blnFirstFound As Boolean, blnLastFound As Boolean, blnIsHidden As Boolean Set relativCell = Worksheets("Beräkning").Cells.Find("Rel.", LookIn:=xlValues) 'here I start to look for the first and last cell that is equal to zero. Do Until IsEmpty(relativCell.Offset(i, 0)) = True Or blnLastFound = True If blnFirstFound = False Then If relativCell.Offset(i, 0) = 0 Then blnFirstFound = True k = i Else: End If End If If blnFirstFound = True And blnLastFound = False Then j = i If relativCell.Offset(j + 1, 0) < 0 Then blnLastFound = True Else: End If Else: End If i = i + 1 'längst ner i listan Loop 'here i have stopped looking for the last cell 'here I select the rows that have cells that are equal to zero startRow = relativCell.Offset(k, 0).Row endRow = relativCell.Offset(j, 0).Row Rows("" & startRow & ":" & endRow & "").Select 'here i want to hide the rows If blnIsHidden = False Then Selection.EntireRow.Hidden = True 'gömmer rader Else End Sub my problem is that how do you write the code for unhiding the values? i know you can write: Selection.EntireRow.Hidden = False but will this work? I dont want to search for zeros when unhiding. but how do you solve this? i though about using a boolean to check if the rows are hidden or not but I do not know how to proceed. please help me if you can! thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding/Unhiding rows
Hi
You can test if a row is hidden or not like If Rows(1).EntireRow.Hidden Then MsgBox "Hidden" Else MsgBox "Visible" End If By using this test you could toggle the row from visible to hidden and back. -- Cheers Nigel "Arne Hegefors" wrote in message ... I have a button that when the user presses the button the program will search an ordered column for values equal to 0 and hide the rows that has values equal to zero. If the rows already are hidden then they shall be revealed by pressing the button. My code is: Sub showHideButton_Klicka() Dim relativCell As Range Dim i As Integer, j As Integer, k As Integer Dim blnFirstFound As Boolean, blnLastFound As Boolean, blnIsHidden As Boolean Set relativCell = Worksheets("Beräkning").Cells.Find("Rel.", LookIn:=xlValues) 'here I start to look for the first and last cell that is equal to zero. Do Until IsEmpty(relativCell.Offset(i, 0)) = True Or blnLastFound = True If blnFirstFound = False Then If relativCell.Offset(i, 0) = 0 Then blnFirstFound = True k = i Else: End If End If If blnFirstFound = True And blnLastFound = False Then j = i If relativCell.Offset(j + 1, 0) < 0 Then blnLastFound = True Else: End If Else: End If i = i + 1 'längst ner i listan Loop 'here i have stopped looking for the last cell 'here I select the rows that have cells that are equal to zero startRow = relativCell.Offset(k, 0).Row endRow = relativCell.Offset(j, 0).Row Rows("" & startRow & ":" & endRow & "").Select 'here i want to hide the rows If blnIsHidden = False Then Selection.EntireRow.Hidden = True 'gömmer rader Else End Sub my problem is that how do you write the code for unhiding the values? i know you can write: Selection.EntireRow.Hidden = False but will this work? I dont want to search for zeros when unhiding. but how do you solve this? i though about using a boolean to check if the rows are hidden or not but I do not know how to proceed. please help me if you can! thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding/Unhiding rows
yes thanks but the macro works like this. If the user presses a button then
the rows hide/unhide and that is the end of it. After having run the macro how can I know where to look for hidden rows? they are not at the same place every time so just refering to a certain row etc. will not work. Is there any way of "remebering" which rows where hidden/unhidden so that the next time the user presses the buttonthe program knows what rows to look for? would really appreciate help! thanks alot! "Nigel" skrev: Hi You can test if a row is hidden or not like If Rows(1).EntireRow.Hidden Then MsgBox "Hidden" Else MsgBox "Visible" End If By using this test you could toggle the row from visible to hidden and back. -- Cheers Nigel "Arne Hegefors" wrote in message ... I have a button that when the user presses the button the program will search an ordered column for values equal to 0 and hide the rows that has values equal to zero. If the rows already are hidden then they shall be revealed by pressing the button. My code is: Sub showHideButton_Klicka() Dim relativCell As Range Dim i As Integer, j As Integer, k As Integer Dim blnFirstFound As Boolean, blnLastFound As Boolean, blnIsHidden As Boolean Set relativCell = Worksheets("Beräkning").Cells.Find("Rel.", LookIn:=xlValues) 'here I start to look for the first and last cell that is equal to zero. Do Until IsEmpty(relativCell.Offset(i, 0)) = True Or blnLastFound = True If blnFirstFound = False Then If relativCell.Offset(i, 0) = 0 Then blnFirstFound = True k = i Else: End If End If If blnFirstFound = True And blnLastFound = False Then j = i If relativCell.Offset(j + 1, 0) < 0 Then blnLastFound = True Else: End If Else: End If i = i + 1 'längst ner i listan Loop 'here i have stopped looking for the last cell 'here I select the rows that have cells that are equal to zero startRow = relativCell.Offset(k, 0).Row endRow = relativCell.Offset(j, 0).Row Rows("" & startRow & ":" & endRow & "").Select 'here i want to hide the rows If blnIsHidden = False Then Selection.EntireRow.Hidden = True 'gömmer rader Else End Sub my problem is that how do you write the code for unhiding the values? i know you can write: Selection.EntireRow.Hidden = False but will this work? I dont want to search for zeros when unhiding. but how do you solve this? i though about using a boolean to check if the rows are hidden or not but I do not know how to proceed. please help me if you can! thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding/unhiding rows | Excel Worksheet Functions | |||
Hiding/Unhiding Rows Automatically | Excel Worksheet Functions | |||
hidden rows but not from hiding/unhiding? | Excel Discussion (Misc queries) | |||
unhiding and hiding rows | Excel Discussion (Misc queries) | |||
Hiding/unhiding rows | Excel Discussion (Misc queries) |