Macro for hiding/unhiding a set of rows

[GoBucks]

I looked for a similar ? in another post but could not find an exact match. I
am new to Excel Macros and was looking to add 3 macros to a worksheet that
will be run by 3 seperate objects (rectangle shapes) in the same worksheet.
My worksheet has a number of header rows and rows containing project level
data by person (Billing Hrs by Month) which begins on row 12 and goes down to
row 211 (= 200 available rows). By default, I am hiding rows 32 to 211 so
that only 20 rows appear.

I would like to add the following:

Macro for Object #1: Show Next 20 Rows
- So if rows 32 to 211 were hidden, the macro would unhide rows 32 to 51. If
clicked again, it would unhide rows 52 to 71, etc. Basically, it would look
to see what the last unhidden row in a range, and then unhide the next 20.

Macro for Object #2: Show All Additional Rows
- Unhide rows 32 to 211

Macro for Object #3: Hide Additional Rows
- Hide rows 32 to 211

This template will be used for tracking different projects and all will have
varied # of individuals working on them. I wanted the user to have the
flexibility to add more rows for larger projects. Hope this makes sense.

 

[ND Pard]

Sub Macro_for_Obect_1()

Dim i As Integer
i = 32

Do While i <= 212
If Rows(i).EntireRow.Hidden = True Then
Range(Rows(i), Rows(i + 19)).EntireRow.Hidden = False
Exit Sub
End If
i = i + 20
Loop

End Sub

Sub Macro_for_Object_2()
Range("A32:A211").EntireRow.Hidden = False
End Sub

Sub Macro_for_Object_3()
Range("A32:A211").EntireRow.Hidden = True
End Sub

Good Luck

 

[GoBucks]

Thank you! I will give these a try.

 

[JLGWhiz]

If I read your post correctly, you wanted to use a command button to run the
macro that gets the next twenty rows. Create a CommandButton1, or change
the name in the code, and try this:

Private Sub CommandButton1_Click()
Set srcRng = Range("A12:A211") 'Define the range
r = 12 'enumerate beginning row
'Find the last visible row in the range
s = srcRng.SpecialCells(xlCellTypeVisible).Count
'Get the next 20 rows
Range("A" & s + r).Resize(20, 1).EntireRow.Hidden = False
End Sub