[QUESTION] VBA CODE HELP: Only one toggle button to unhide one row at a time (there are 20 rows, but don't want them all unhidden at once)

[joekow]

Hi,

I have be looking all over the internet trying to find out who on earth figured this out. I don't know what to call this process or what others are calling it to effectively find the answer. I thought using keywords like: looping, repeat, one row at a time, continuous, and continuously alongside the keywords like vba, excel, vba coding, excel vba, vba excel and so on, would pull up the solution to my situation, but have found no such situation nor solution. And, I know I can't possibly be the only wanting to achieve this; I am obviously using the wrong words, but I don't know what else to use to define it.

Anyway, I have stopped my long hours search to just stop by a forum I think would be full of VBA Excel experts and point me in the right direction or with a solution. I am using the 2007 version, if that matters.

And, I thought I was onto something with the code below, but I need the toggle button to be able to continuously unhide a single row one at a time--that I have hidden--every time it is clicked upon. I have 20 rows that I have hidden by default. Once the rows have been unhidden one at a time by clicking the toggle button, then it should not rehide the row.

Will some one please help me out?

This works for only unhiding a single row at a time for two hidden rows:

Private Sub ToggleButton1_Click()

If ToggleButton1 Then
     Rows(76).EntireRow.Hidden = False
 Else
     Rows(77).EntireRow.Hidden = False
 End If


End Sub

This does not work for only unhiding a single row at a time for more than two hidden rows:

Private Sub ToggleButton1_Click()

If ToggleButton1 Then
     Rows(76).EntireRow.Hidden = False
 Else
     Rows(77).EntireRow.Hidden = False
 Else
     Rows(78).EntireRow.Hidden = False
 End If


End Sub

Thank you in advance.

 

[Worf]

Hi and welcome to the Board
See if this is useful, it will unhide the first hidden row from the column top:

Private Sub CommandButton1_Click()


Rows(Range("k1:k50").SpecialCells(12).Areas(1).Rows.Count + 1).Hidden = False


End Sub

 

[RickXL]

First, I would use an ordinary ActiveX pushbutton rather than a toggle button.

Then this code should work:

Private Sub CommandButton1_Click()    Dim iRow As Long
    Dim LastRow As Long
    Dim ws1 As Worksheet
    Set ws1 = Sheet1

    'Find last row - even if hidden
    LastRow = ws1.UsedRange.Rows.Count

    'Loop over used rows
    For iRow = 1 To LastRow
        If ws1.Rows(iRow).Hidden = True Then
            ws1.Rows(iRow).Hidden = False
            Exit For
        End If
    Next
End Sub

Basically, it finds the last row in the UsedRange.
Then it loops over those rows unhiding the first hidden row with each button push.