Merge Same Cells
VBA

VBA: Quickly merge cells with same value in Excel

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.

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
Record Macro
  • Record Macro window will be opened.
  • Select “Personal Macro Workbook” in Store macro in drop-down.
Record Macro window
Record Macro window
  • Go to the View Tab>>Macros>>Stop Recording.
Stop Recording
Stop Recording
Personal.xlsb
Personal.xlsb
  • Macro1 is available in Module1
Module 1
Module 1
  • 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

 

Merge Same Cells macro
Merge Same Cells macro

 

  • Now select your data wherein you want to merge the same cells.
Select your range
Select your range
  • Go to View >> Macros >> View Macros
  • Select the Personal.xlsb in “Macros In” drop-down
  • Select Merge_Same_Cells macro.
  • Click on Run button.
Run the macro
Run the macro
  • Cells with the same values will be merged.
Cells have been merged
Cells have been merged

Click here to download the practice workbook.

 

Watch the step by step video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com