Sort And Merge Groups In Table Excel VBA Macro

In this post we see how to sort and merge a group of cells with the same value in Excel using VBA macros. Selected values are sorted in ascending order, as per the value in column A first, and then column B. Then, the groups of contiguous cells with the same value are merged.

Macro code:

 

  Sub SortAndMergeContiguousGroups()

   

  Dim startRow As Integer, endRow As Integer

 

  'Sort the table

  Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, _

  Key2:=Range("B1"), Order2:=xlAscending, Header:=xlYes

 

  'Merge groups with same value

  Range("A1").CurrentRegion.Select

  startRow = Selection.Row

  Application.DisplayAlerts = False

  For endRow = Selection.Row To Selection.Row + Selection.Rows.Count - 1

      If Cells(endRow, Selection.Column).Value <> Cells(endRow + 1, Selection.Column).Value Then

          Range(Cells(startRow, Selection.Column), Cells(endRow, Selection.Column)).Merge

          startRow = endRow + 1

      End If

  Next endRow

  Application.DisplayAlerts = True

  

  End Sub

 

 

Macro explained:

• We declare two integers (startRow  and endRow) to set the start and end row number when looping through the data set.
• Then we apply the Sort method to the current region of the data set (around cell A1). The CurrentRegion property of the Range object gets the region with data delimited by any blank rows and columns.
• The Sort method accepts up to three keys. Each key corresponds to a column to be sorted, and for each key, the order can be set to either xlAscending or xlDescending. In this case, we sort by column A (key1) and then B (key2), and both keys in ascending order. Additionally, we set the header parameter to True to skip sorting the header.
• The second part of the macro loops through the data set to find and merge contiguous cells with the same value. First, we select the table, i.e., the current region of the data set, and from that point we work with the Selection object.
• Initially, we set the startRow variable to the first row in the selected table. That’s the starting row of the range with the values to be merged. However, startRow will change throughout the loop when cells for each row do not have the same value.
• Before starting the loop, we set hide alerts by setting DisplayAlerts to False, in order to avoid the message or warning that Excel pops-up when trying to merge cells that are not empty.
• Now we start a loop from the first row to the last row in the selected range. For each row, we check if the value in the first column of the selection is different than the value in the next row. In that case, we merge the values from startRow to that row (endRow). Note that, when there is a single cell, the merging does not have any effect. That will happen with the first row (the header), for example.
• Every time we merge a group, the value of startRow changes to the next row after the merged range. It is also important to understand, that we only merge cells in the first column of the selected table. But this can be easily leveraged to merge cells in the other column or more columns in a larger dataset. In that case, we could probably have two loops, and repeat the process for each column in the table.

This is how we sort and merge groups of cells in Excel using VBA macros.

 

Other examples:

• How to Add Buttons To Delete Rows with Excel VBA macros
  
• How to Compare Two Workbooks with Excel VBA macros
  
• How to Copy/Paste Charts To PowerPoint with Excel VBA macros