Home>Blogs>VBA>VBA: Quickly merge cells with same value in Excel
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.

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
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.
Merge cells with same value in Excel
Merge cells with same value in Excel

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

Watch the step by step video tutorial:

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com