My Blog

Grouping/UnGrouping Data

In continuation from here

To understand this example, let’s take a bigger dataset. For example

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

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/2011" : .Range("B3").Value = "1/10/2011" : .Range("B4").Value = "1/20/2011"
        .Range("B5").Value = "1/31/2011" : .Range("B6").Value = "1/6/2011" : .Range("B7").Value = "1/10/2011"
        .Range("B8").Value = "1/20/2011" : .Range("B9").Value = "1/31/2011" : .Range("B10").Value = "1/6/2011"
        .Range("B11").Value = "1/10/2011" : .Range("B12").Value = "1/20/2011" : .Range("B13").Value = "1/31/2011"
        .Range("B14").Value = "1/6/2011" : .Range("B15").Value = "1/10/2011" : .Range("B16").Value = "1/20/2011"
        .Range("B17").Value = "1/31/2011"

        .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  Group > Rows or Cells from the Data Tab in Excel 2010.

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/2011" : .Range("B3").Value = "1/10/2011" : .Range("B4").Value = "1/20/2011"
        .Range("B5").Value = "1/31/2011" : .Range("B6").Value = "1/6/2011" : .Range("B7").Value = "1/10/2011"
        .Range("B8").Value = "1/20/2011" : .Range("B9").Value = "1/31/2011" : .Range("B10").Value = "1/6/2011"
        .Range("B11").Value = "1/10/2011" : .Range("B12").Value = "1/20/2011" : .Range("B13").Value = "1/31/2011"
        .Range("B14").Value = "1/6/2011" : .Range("B15").Value = "1/10/2011" : .Range("B16").Value = "1/20/2011"
        .Range("B17").Value = "1/31/2011"

        .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