In continuation from here

We already covered here how to create the form, set references and place the required buttons. Now we will understand how grouping and ungrouping works.

To understand this example, let’s create a bigger dataset which will look like what we have below.

So if we ran the below code, we will get an output as shown in the image above.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month" : .Range("B1").Value = "Date" : .Range("C1").Value = "Money Spent"
            .Range("A2:A5").Value = "January" : .Range("A6:A9").Value = "February"
            .Range("A10:A13").Value = "March" : .Range("A14:A17").Value = "April"

            .Range("B2").Value = "1/6/2018" : .Range("B3").Value = "1/10/2018" : .Range("B4").Value = "1/20/2018"
            .Range("B5").Value = "1/31/2018" : .Range("B6").Value = "1/6/2018" : .Range("B7").Value = "1/10/2018"
            .Range("B8").Value = "1/20/2018" : .Range("B9").Value = "1/31/2018" : .Range("B10").Value = "1/6/2018"
            .Range("B11").Value = "1/10/2018" : .Range("B12").Value = "1/20/2018" : .Range("B13").Value = "1/31/2018"
            .Range("B14").Value = "1/6/2018" : .Range("B15").Value = "1/10/2018" : .Range("B16").Value = "1/20/2018"
            .Range("B17").Value = "1/31/2018"

            .Range("C2").Value = "200" : .Range("C3").Value = "350" : .Range("C4").Value = "155"
            .Range("C5").Value = "345" : .Range("C6").Value = "213" : .Range("C7").Value = "231"
            .Range("C8").Value = "321" : .Range("C9").Value = "341" : .Range("C10").Value = "231"
            .Range("C11").Value = "345" : .Range("C12").Value = "432" : .Range("C13").Value = "124"
            .Range("C14").Value = "543" : .Range("C15").Value = "342" : .Range("C16").Value = "222"
            .Range("C17").Value = "215"

            '~~> Autofitting text in columns
            .Columns("A:C").EntireColumn.AutoFit()
        End With
    End Sub

This is a very simple dataset but good enough to achieve what we want. Sometimes when we have huge dataset, we Hide/Show Columns/Rows by right clicking on them and then clicking on hide to see the relevant data. But there is another feature in Excel; Grouping and Ungrouping. If you were in Excel then all you need is to select cells which you want to group and then click on Data TabGroup | Rows or Cells as shown below.

Now suppose we want to group data on the basis of months. So this is how data will look like after grouping. You can click on the +/ signs to show hide your data.

To achieve this, we can use this code.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month" : .Range("B1").Value = "Date" : .Range("C1").Value = "Money Spent"
            .Range("A2:A5").Value = "January" : .Range("A6:A9").Value = "February"
            .Range("A10:A13").Value = "March" : .Range("A14:A17").Value = "April"

            .Range("B2").Value = "1/6/2018" : .Range("B3").Value = "1/10/2018" : .Range("B4").Value = "1/20/2018"
            .Range("B5").Value = "1/31/2018" : .Range("B6").Value = "1/6/2018" : .Range("B7").Value = "1/10/2018"
            .Range("B8").Value = "1/20/2018" : .Range("B9").Value = "1/31/2018" : .Range("B10").Value = "1/6/2018"
            .Range("B11").Value = "1/10/2018" : .Range("B12").Value = "1/20/2018" : .Range("B13").Value = "1/31/2018"
            .Range("B14").Value = "1/6/2018" : .Range("B15").Value = "1/10/2018" : .Range("B16").Value = "1/20/2018"
            .Range("B17").Value = "1/31/2018"

            .Range("C2").Value = "200" : .Range("C3").Value = "350" : .Range("C4").Value = "155"
            .Range("C5").Value = "345" : .Range("C6").Value = "213" : .Range("C7").Value = "231"
            .Range("C8").Value = "321" : .Range("C9").Value = "341" : .Range("C10").Value = "231"
            .Range("C11").Value = "345" : .Range("C12").Value = "432" : .Range("C13").Value = "124"
            .Range("C14").Value = "543" : .Range("C15").Value = "342" : .Range("C16").Value = "222"
            .Range("C17").Value = "215"

            '~~> Insert rows to create header for month
            .Range("6:6,10:10,14:14").Insert(Shift:=Excel.XlDirection.xlDown, _
            CopyOrigin:=Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove)

            '~~> Create Month Headers
            .Range("A6").Value = "January" : .Range("A10").Value = "February"
            .Range("A14").Value = "March" : .Range("A21").Value = "April"

            '~~> Group the relevant rows
            .Rows("2:5").Rows.Group()
            .Rows("7:10").Rows.Group()
            .Rows("12:15").Rows.Group()
            .Rows("17:20").Rows.Group()

            '~~> Autofitting text in columns
            .Columns("A:C").EntireColumn.AutoFit()
        End With
    End Sub