Group contiguous rows separately

[Qqqqq]

Is there a way to group rows that are next to each other separately? For example, can I get seperate groups for rows 6:14, 15:24, 25:34, and 35:44, without having Excel combine them into one single grouping?

I don't want to have insert a blank row between the groups. Here's why...

My worksheet has a header (rows 1:5), and sections below for each quarter. I only want to show the current quarter, and collapse each of the non-current quarters. If I leave a blank cell between each group, the first quarter information shows right under the header, but when I collapse the first quarter and show only the second quarter, the blank row creates a space between the header and the information. The third quarter has two rows between the header and the information, and the fourth quarter has a three row gap between the header and the information. The information is linked into a PowerPoint slide, and I want the spacing between the header and the information to be the same, regardless of which quarter's report is being displayed. It is also used in a printed report, which needs to be consistently formatted each quarter.

Is there a way to accomplish contiguous grouping, or perhaps a better way to do this? Having to manually group and ungroup each quarter feels like it defeats the convenience factor of grouping.

 

[Yongle]

you could use VBA

Here is one way
In a cell (A1 is used in this example) add data validation (allowing LIST) with SOURCE: ALL,Q1,Q2,Q3,Q4
Use Worksheet_Change event to trigger the rows to be shown or hidden when a different value is selected in cell

The code goes in the SHEET's code window
(rightclick on sheet nametab \ click view code \ paste code into open window)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A1" Then
        Dim r As String
        Const default = "6:44"
        Rows(default).Hidden = True
            Select Case Target
                Case "Q1":      r = "6:14"
                Case "Q2":      r = "15:24"
                Case "Q3":      r = "25:34"
                Case "Q4":      r = "35:44"
                Case Else:      r = default
            End Select
        Rows(r).Hidden = False
    End If
End Sub

And save the workbook as macro enabled

 

[Qqqqq]

Yongle, I used the code almost as you provided, but want to use the date entered into the header in cell A3 instead of a data validation list in cell A1. The code I entered now looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A3" Then
        Dim r As String
        Const default = "6:44"
        Rows(default).Hidden = True
            Select Case Month(Target)
                Case Is <=3:        r = "6:14"
                Case Is <=6:        r = "15:24"
                Case Is <=9:        r = "25:34"
                Case Is <=12:      r = "35:44"
                Case Else:      r = default
            End Select
        Rows(r).Hidden = False
    End If
End Sub

Unfortunately, when I try changing the date in cell A3, nothing happens, so it doesn't seem to be working for me. Although I've dabbled with VBA a little, I don't really understand this code. How is it supposed to work? What might I need to change to make this work?

 

[Yongle]

code now triggered when value in A3 is amended
- rows determined by month number based on valid date value in A3

Try this
- error handling is now required to prevent code failing if A3 does not contain a valid date
- delete value in A3 to make all rows visible

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A3" Then
        Dim r As String
        On Error Resume Next
        Const default = "6:44"
        Rows(default).Hidden = True
        MsgBox Month(Target)
            Select Case Month(Target)
                Case 1 To 3:    r = "6:14"
                Case 4 To 6:    r = "15:24"
                Case 7 To 9:    r = "25:34"
                Case Else:      r = "35:44"
            End Select
        If IsEmpty(Target) Then r = default
        Rows(r).Hidden = False
    End If
    On Error GoTo 0
End Sub

 

[Qqqqq]

Yongle, I updated the code as you provided, but still when I delete, enter, or update the date in cell A3 nothing happens.

 

[Yongle]

It works for me so something is different in your workbook

where did you put the code?

 

[Qqqqq]

The code is in the worksheet's code window.

 

[Yongle]

Is the VBA being triggered?

Below this line

If Target.Address(0, 0) = "A3" Then

insert this line

MsgBox Date

Amend value in A3
If message box does not appear then your code is not being triggered

 

[Qqqqq]

No, the code is not being triggered.

 

[Yongle]

If the code does not trigger then you have either placed the code in the wrong place ( but you have already confirmed that it was placed on the sheet's code window which is the correct location) or Events are disabled or perhaps all macros are disabled in your workbook


1. Insert this sub in a module (like Module1) and run it to re-enable events

Sub ee()
    Application.EnableEvents = True
End Sub

2. next add this code in a sheet code window and then select a different cell on the sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox Date
End Sub

If the message box does not appear, try step 2 again in a new workbook
If the message box does not appear, post a screen dump of the top of the VBA editor ( the line ABOVE all the icons which begins with Microsoft Virtual Basic ... and includes the file name etc)