vba to unhide all rows before running another code

[CamC]

Hi there,
I am quite new in vba and I am trying to run 2 macros using 2 buttons (shapes forms) in the same sheet (1). All the buttons have already been assigned to the respective macro.

First button: the macro (Monthly Tasks) is working fine and is hiding the rows ("17:19, 31:57").
The issue is the second button. In this macro (Quarterly Tasks) I need to unhide all the rows that was hidden after clicking the first button (macro Monthly Tasks) and then hide rows ("18:20, 22:30"). I would like to click on second button and it unhide (the rows from previous button) and hide rows ("18:20, 22:30") to happen at the same time.
Is anyone able to help me this situation?

Much appreciated

 

[kevin9999]

Assign this code to the second button:

Sub Second_Button()
    With ActiveSheet
        Union(.Range("17:19"), .Range("31:57")).EntireRow.Hidden = False
        Union(.Range("18:20"), .Range("22:30")).EntireRow.Hidden = True
    End With
End Sub

 

[smozgur]

Welcome to the MrExcel Message Board!

Since you mentioned that you are new to VBA, I'll suggest using the Macro Recorder to start doing these routines. It is a really helpful tool.

Just start recording the macro (Developer->Record Macro), select entire rows that you'd like to hide, then right-click on the selection and click on the Hide context menu item. Stop recording, and it will give you a result something like this for the rows "17:19".

Sub Macro1()
    Rows("17:19").Select
    Selection.EntireRow.Hidden = True
End Sub

The macro recorder does everything in detail as we applied during the recording. However, we don't need the macro to select the range to be hidden prior to the action but directly apply the action. We can change this recorded macro to the following:

Sub Macro1()
    Rows("17:19").EntireRow.Hidden = True
End Sub

Now, we have two different areas, so we need to alter this code a bit more to refer to multiple areas. However, the Rows property doesn't work with multiple areas. This is where the Range property pops up:

Sub Macro1()
    Range("17:19,31:57").EntireRow.Hidden = True
End Sub

Perfect! Wait, you wanted this range to be displayed. So, all we need to do is change the hidden property to be False to do that.

Sub Macro1()
    Range("17:19,31:57").EntireRow.Hidden = False
End Sub

And the final step to hide the other range as you would guess already:

Sub Macro1()
    ' Show the previously hidden rows
    Range("17:19,31:57").EntireRow.Hidden = False
    ' Hide the following rows
    Range("18:20, 22:30").EntireRow.Hidden = True
End Sub

Hope this helps.

 

[CamC]

Thank you so much Kevin and smozgur both suggestion worked exactly the way I wanted.
great great help!!!

 

[smozgur]

Glad to hear we were able to help.

To help future readers, that would be great if you could mark the post as the solution that helped you in the best way to solve the question by clicking on the checkmark button on the right side of the post. More information: Mark as Solution.

 

[My Aswer Is This]

This script will Toggle the range from Hidden to Unhidden

Sub Hide_Unhide_Rang()
'Modified  6/24/2022  8:19:53 AM  EDT
Range("17:19,31:57").EntireRow.Hidden = Not Range("17:19,31:57").EntireRow.Hidden
End Sub