Merge cells with the same values

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.

This is going to give us the following result.

Code explanation:

The following line of code disables alerts.

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.

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.

If the condition is met the cells are merged and centered vertically.

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.

Posted in vba