[RESOLVED] Toggle hide/unhide rows button

[lionelnz]

Good evening from NZ.

I have a command button that hides rows based on zero value in Col B
then a 2nd btn to unhide those rows. I then tried combiningthese to create a toggle button hide/umhide rows. (Read on McDuff.)

The challenge is that as I want this code for several workbooks that
will have data of varying row lengths & not all the zero values will
be in the exact same Cell (but range will be similar) for each workbook or each month.

Having done a little research on the Board (& googled) I have not
found an answer to my challenge. So I am sure someone will point me
in the right direction. (I have tried combing the 2 macros using a
flag & the syntax must be right as no error but it doesn't toggle
hide/unhide rows,)

Private Sub CommandButton1_Click()
'Hide rows with value equal zero

BeginRow = 3
EndRow = ActiveSheet.UsedRange.Rows.Count
ChkCol = 2
Range("B3:B" & EndRow).EntireRow.Hidden = True

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value <> 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
Range("A2").Activate

End Sub

Private Sub CommandButton2_Click()
'Unhide all rows

BeginRow = 3
EndRow = ActiveSheet.UsedRange.Rows.Count
ChkCol = 2
Range("B3:B" & EndRow).EntireRow.Hidden = False
Range("A2").Activate


End Sub

Private Sub CommandButton3_Click()
'Toggles rows with value equal zero
Dim flg As Boolean
flg = True

BeginRow = 3
EndRow = ActiveSheet.UsedRange.Rows.Count
ChkCol = 2

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).EntireRow.Hidden = True Then
flg = False
Exit For
Else
flg = True
End If
Next RowCnt

If flg = False Then
Range("B3:B" & EndRow).EntireRow.Hidden = False
Else
'Cells(RowCnt, ChkCol).EntireRow.Hidden = True
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value <> 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End If

Range("A2").Activate

End Sub

[zaza]

Have you tried actually going through and checking what your code is doing?
It looks to me like the following:

1) Set the start and end rows.
2) Loop through all the rows
3) When you find the first hidden row, set the flag false and jump out.
4a) If the flag is false (i.e. it has jumped out) then set the last row as visible
4b) If the flag is not false (i.e. there are no hidden rows) then loop through all the rows and unhide any that do not have 0 in column 2.



If this makes sense, and is what you want it to do, then I see no reason why it shouldn't work. However, I suspect that it isn't. The easiest way to do it is to use a togglebutton or set a cell somewhere to keep track of whether you are hiding or unhiding. Then you need two branches; if hiding then go through the whole list and hide any rows where column 2 = 0 (i.e. the code in button 1), if unhiding then go through all the rows and unhide. Note that you may actually want to check the value of column 2 before unhiding as well - otherwise other hidden rows will automatically become visible even though they were not hidden by your toggle button.


zaza

[lionelnz]

Thanks ZAza. That is exactly what I want to do & I think my code reflects that but obviously my logic & VBA logic are different in this case. However all is not lost as I have used a nested if function to resolve it.

Private Sub CommandButton1_Click()
'Togles Hide/Unhide rows with value equal zero

BeginRow = 3
EndRow = ActiveSheet.UsedRange.Rows.Count
ChkCol = 2

For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).EntireRow.Hidden = True Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    Else
        If Cells(RowCnt, ChkCol).Value <> 0 Then
        Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        Else
        Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    End If

Next RowCnt
    Range("A2").Activate

End Sub