Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for hiding/unhiding a set of rows
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for hiding/unhiding a set of rows
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for hiding/unhiding a set of rows
Thank you! I will give these a try.
"ND Pard" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for hiding/unhiding a set of rows
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 "GoBucks" wrote in message ... Thank you! I will give these a try. "ND Pard" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding/unhiding rows | 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) | |||
Hiding/Unhiding rows | Excel Programming |