In this article you will learn how to quickly merge same cells in Excel using VBA. Using this VBA code, you can merge multiple columns data together. This trick can save a lot of time. Just select the range for which you want to merge the cells with same values and run this macro. You can copy this code to the personal macro so that it will available for every workbook.
Merge cells with same value in Excel
To create a personal macro first of all we have enable the Personal.xlsb in Visual Basic Editor. Below are the steps to enable the Personal.xlsb in Visual Basic Editor-
- Go to the View Tab>>Macros>>Record Macro..
- Record Macro window will be opened.
- Select “Personal Macro Workbook” in Store macro in drop-down.
- Go to the View Tab>>Macros>>Stop Recording.
- Now press Alt+F11 to open Visual Basic Editor.
- Personal.xlsb will be available in VBE.
- Macro1 is available in Module1
- Delete the entire code available in Module1 and copy below given code.
Option Explicit Sub Merge_Same_Cells() Application.DisplayAlerts = False Dim rng As Range MergeCells: For Each rng In Selection If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then Range(rng, rng.Offset(1, 0)).Merge Range(rng, rng.Offset(1, 0)).HorizontalAlignment = xlCenter Range(rng, rng.Offset(1, 0)).VerticalAlignment = xlCenter GoTo MergeCells End If Next End Sub
- Now select your data wherein you want to merge the same cells.
- Go to View >> Macros >> View Macros
- Select the Personal.xlsb in “Macros In” drop-down
- Select Merge_Same_Cells macro.
- Click on Run button.
- Cells with the same values will be merged.
Visit our YouTube channel to learn step-by-step video tutorials
