Grouping and ungrouping rows in excel using VBA assigning x1 button for opening and closing

Barry18180

New Member
Joined
Apr 12, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Afternoon all,

I'm hoping somebody can help me with what I think could be a simple task?

I would like to ungroup and group certain rows using VBA so I can make my document more user friendly for the end user

I have tried a number of solutions but none seem to work. I want to click on Monday once to ungroup the rows. Then click it again to group the rows back up, then continue to do this for the different days of the week. I believe I will need to have a small macro for each day to keep it simple

If anybody can assist them please let me know

kind regards

Barry
 

Attachments

  • Capture.PNG
    Capture.PNG
    22.5 KB · Views: 11

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
So how are we to know what rows are for Monday.
Is "Monday" in column "A" of the row?
 

Barry18180

New Member
Joined
Apr 12, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
So how are we to know what rows are for Monday.
Is "Monday" in column "A" of the row?
Sub Monday()

Dim rng As Range
Set rng = Application.Range("a34:a80")

If rng.EntireRow.Hidden = True Then
rng.EntireRow.Hidden = False
Else: rng.EntireRow.Hidden = True
End If

End Sub

Sub T()

Dim rng As Range
Set rng = Application.Range("a82:a128")

If rng.EntireRow.Hidden = True Then
rng.EntireRow.Hidden = False
Else: rng.EntireRow.Hidden = True
End If

End Sub
Sub Wednesday()

Dim rng As Range
Set rng = Application.Range("a130:a176")

If rng.EntireRow.Hidden = True Then
rng.EntireRow.Hidden = False
Else: rng.EntireRow.Hidden = True
End If

End Sub
Sub Thursday()

Dim rng As Range
Set rng = Application.Range("a178:a224")

If rng.EntireRow.Hidden = True Then
rng.EntireRow.Hidden = False
Else: rng.EntireRow.Hidden = True
End If

End Sub
Sub Friday()

Dim rng As Range
Set rng = Application.Range("a226:a259")

If rng.EntireRow.Hidden = True Then
rng.EntireRow.Hidden = False
Else: rng.EntireRow.Hidden = True
End If

End Sub
 
Solution

Barry18180

New Member
Joined
Apr 12, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
This works perfectly, thank you very much for getting back to me. I hope this helps someone else in the future
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
You could do it this way:
VBA Code:
Sub Monday()
'Modified  4/13/2021  7:59:34 AM  EDT
Rows("34:80").Hidden = Not Rows("34:80").Rows.Hidden
End Sub
Sub Tuesday()
Rows("82:128").Hidden = Not Rows("82:128").Rows.Hidden
End Sub
Sub Wednesday()
Rows("130:176").Hidden = Not Rows("130:176").Rows.Hidden
End Sub
Sub Thursday()
Rows("178:224").Hidden = Not Rows("178:224").Rows.Hidden
End Sub
Sub Friday()
Rows("226:259").Hidden = Not Rows("226:259").Rows.Hidden
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,323
Messages
5,641,530
Members
417,215
Latest member
Diaryman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top