CODEWITHSUNDEEP
excelvba
Joel Jacob Mathew
Joel Jacob Mathew

Reputation: 1

unhide multiple rows with toggle button

Quite new to VBA and been learning off the internet. I have hidden rows in Excel spreadsheet and want them to unhide when I click the toggle button which I have named "ADD ROWS". So when I click it keeps unhiding which is great, but I like it to unhide 3 rows at a time. At the moment it keeps unhiding one row at a time. Note : I am not trying to HIDE rows. Just UNHIDE.

Current Macro in the button is : 

Sub Affiliates_Button4_Click()
  Dim i As Long
  If Columns("n").SpecialCells(xlCellTypeVisible).Count = Rows.Count Then Exit Sub
  i = 8 + 1
  Do Until Rows(i).Hidden = True
    i = i + 1
  Loop
  Rows(i).Hidden = False
End Sub

Upvotes: 0

Views: 827

Answers (1)

Phill Treddenick
Phill Treddenick

Reputation: 1420

Loop until through all the rows until you find 3 hidden rows to unhide. You should also have some upper limit on the number of rows to check. The max number of rows in Excel is 1048576.

  Sub Affiliates_Button4_Click()
  Dim i As Long
  Dim j As Long
  j = 0
  i = 8 + 1
  Do Until j > 2 Or i >= 1048576
    i = i + 1
    If Rows(i).Hidden = True Then
        j = j + 1
        Rows(i).Hidden = False
    End If
  Loop
End Sub

Upvotes: 1

Related Questions