Group Rows With Same Format Excel VBA Macro

In this post we see how to group cells or rows with the same format in Excel using VBA macros. Grouping cells or rows in Microsoft Excel is quite helpful when dealing with lots of data arranged in categories, phases, etc., which we may need to monitor or keep updating regularly. Then, we can collapse groups when complete or when not relevant at a given time, thus making space and setting focus on the target data. The Excel VBA macro loops through the dataset, and groups rows based on the format, specifically, the example below groups rows sharing the same category or headers with bold text. That can be changed to any other format condition such as font size, color, etc.

Macro/VBA code:

 

  Sub GroupCellsSameFormat()

      Dim startr As Integer, endr As Integer, lr As Integer, r As Integer

      lr = Cells(Rows.Count, "A").End(xlUp).Row

      startr = 0: endr = 0

      For r = 2 To lr

          If Range("A" & r).Font.Bold = True Then

              If startr = 0 Then

                  startr = r + 1

              Else

                  endr = r - 1

                  Range(Cells(startr, 1), Cells(endr, 1)).Rows.Group

                  startr = r + 1

              End If

          End If

      Next r

      Range(Cells(startr, 1), Cells(r - 1, 1)).Rows.Group

  End Sub

 

 

Macro explained:

• We declare integer variables to get the value of the last row (lr), each row in the dataset (r), and the start and end rows for each group selected (startr, endr).
• We get the last row with content in the active sheet assigned to the variable lr. We also initialize the startr and endr variables before starting the loop.
• Then we start a For loop through the dataset that assigns each row to the variable r. For each row, the VBA macro checks the format of the corresponding cell in column A. That can be changed to check other cells, the entire row, etc.
• In this example, the macro checks if the font is bold. That can be also changed to any desired format, for example the font size or color, using the corresponding properties of the Font. Another possibility is to look for upper case text vs lower case, that could be done with Excel VBA UCase function.
• The first time it finds a cell formatted bold, it sets the startr variable to the next row in the dataset. The next and following times when finding a cell formatted bold, it will then set the endr variable to the previous row, group everything between startr and endr, and then set startr again to the next row in the dataset.
• The Group method is used to group cells or rows, or also columns, with Excel VBA. In this case, we used Group with rows for a particular range that comprises the cells below the formatted cell in the first column. It is irrelevant if we select the first or other columns, because the Rows property is going to extend the selection to the entire row.
• That groups all the rows in the Excel sheet except the rows after the last formatted row. For that reason, we group rows again after the loop is complete. That will group everything starting at startr and until the last row with content (lr).

This is how we group cells with the same format in Excel with VBA macros.

Other examples:

• How to Compare Two Columns or Lists with Excel VBA macros
  
• How to Protect All Selected Workbooks with Excel VBA macros
  
• How to Import Data From Google Sheets with Excel VBA macros