How to Merge Adjacent Cells in Columns with Same Data in Excel

This post will guide you how to merge adjacent cells in columns with same value or data with VBA Macro in Excel. How do I merge cells when adjacent value is the same in one column in Excel.

Merge Adjacent Cells with Same Data


Assuming that you have a list of data in range A1:B4, and you only want to merge adjacent cells in column A. How to do it. You can use an Excel VBA Macro to quickly merge adjacent cells with same data or value in a specific column or range. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

merge adjacent cells with same data in one column1

 Sub MergeAdjacentCells()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to merge cells with same data in one column:", "MergeAdjacentCells", myRange.Address, Type:=8)

    MergeAgain:
    For Each myCell In myRange
       If myCell.Value = myCell.Offset(1, 0).Value And IsEmpty(myCell) = False Then
           Range(myCell, myCell.Offset(1, 0)).Merge
           GoTo MergeAgain
       End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

merge adjacent cells with same data in one column2

#6 Select one Range that you want to merge cells with same data in one column. Click Ok button.

merge adjacent cells with same data in one column3

#7 let’s see the result:

merge adjacent cells with same data in one column4

 

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar