If you have a worksheet with the same data in adjacent cells, you may want to merge them for visual reasons. In this tutorial, I will show you how you can do it.
In this case, you can select ranges A1:A2 and press the merge button, then do the same for A4:A6. But if you are dealing with hundreds of cells, it’s not the best idea.
Of course, the best way here is to use some VBA code to do the work for us.
First, prepare your data.
The cells that you want to merge must be sorted, otherwise, only cells that are adjacent and have the same value will be merged.
Your sorted example should look like this.
After you sort the data, you can run the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub MergeSimilarCells() Application.DisplayAlerts = False Application.ScreenUpdating = False Set myRange = Range("A1:A6") CheckAgain: For Each cell In myRange If cell.Value = cell.Offset(1, 0).Value And Not IsEmpty(cell) Then Range(cell, cell.Offset(1, 0)).Merge cell.VerticalAlignment = xlCenter GoTo CheckAgain End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub |
This is going to give us the following result.
Code explanation:
The following line of code disables alerts.
1 |
Application.DisplayAlerts = False |
If you don’t add this line, each time the code tries to merge cells, you will get this message.
In our case, Excel will try to merge cells three times and will display this warning each time.
The next line of code is used to disable screen updating each time Excel merge cells. This line of code will improve the performance greatly if you have a lot of data. In won’t do much in our case.
1 |
Application.ScreenUpdating = False |
Then, we run the loop for myRange. This range consists of a single column, so the next cell will always be the new row.
This line checks whether the cell in the next row is equal to the current cell. It also checks if the cell is not empty, otherwise, the loop would go to the last cell inside the worksheets and the program would crash.
1 |
If cell.Value = cell.Offset(1, 0).Value And Not IsEmpty(cell) |
If the condition is met the cells are merged and centered vertically.
1 2 |
Range(cell, cell.Offset(1, 0)).Merge cell.VerticalAlignment = xlCenter |
The next line is the GoTo statement.
It will move the execution before the loop to check if there are more cells to check.
At the end, we are going to set ScreenUpdating and DisplayAlterts to TRUE.