Excel VBA Macro to Merge Adjacent Cells with Matching Values
Question details
The user needs a complete VBA macro to automatically merge consecutive cells that contain the same value across selected columns without having to run the procedure repeatedly.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning and organizing a spreadsheet where consecutive rows within specific columns contain duplicate data that needs to be consolidated into single merged cells.
- Observed behavior
- Currently, merging identical adjacent cells requires repetitive manual actions. The user wants to automate this behavior by using a standard VBA module.
Before running any VBA macro, ensure you have enabled the Developer tab in your ribbon. It is highly recommended to save a backup copy of your workbook, as actions performed by macros cannot be undone using the standard Undo feature.
Use a VBA Loop to Find and Merge Matching Adjacent Cells
Implement a VBA Sub procedure that iterates through your selected columns, compares adjacent rows, and merges them if their values match.
This custom script loops through the selected range to evaluate consecutive cells. When it detects matching values, it merges the range and automatically advances the counter to the next unmatched row, completing the task in a single run.
Ensure you place this code in a standard VBA module rather than a worksheet module or as a User-Defined Function (UDF), otherwise it will not appear in your macros list.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, click on 'Insert' in the top menu bar, and then select 'Module' to create a new standard module.
Copy and paste the following code into the blank module window: Sub MergeSameCell() Dim rng As Range, cell As Range Dim rowCount As Long, i As Long, j As Long Set rng = Application.Selection rowCount = rng.Rows.Count For Each cell In rng.Columns For i = 1 To rowCount For j = i + 1 To rowCount If cell.Cells(i, 1).Value <> cell.Cells(j, 1).Value Then Exit For Next j If j - i > 1 Then rng.Parent.Range(cell.Cells(i, 1), cell.Cells(j - 1, 1)).Merge End If i = j - 1 Next i Next cell End Sub
Close the VBA editor to return to your Excel workbook. Highlight the range of columns where you want to merge identical adjacent values, press ALT + F8, select 'MergeSameCell' from the list, and click 'Run'.
Automate Data Organization Efficiently with WPS Office
WPS Spreadsheet fully supports VBA macros, allowing you to run scripts like the cell-merging macro seamlessly. It offers a familiar interface and robust tools to manage complex data effortlessly.
- 1. Download and Install: Get WPS Office from the official website and install it on your device for free.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsm or .xlsx file containing the data you wish to merge.
- 3. Enable Macros and Run: Go to the Developer tab, open the VBA editor, and insert the merge code exactly as you would in Microsoft Excel.
Frequently Asked Questions
Why isn't my new macro showing up in the Macro list?
If your macro does not appear when you press ALT + F8, ensure it was pasted into a standard VBA module (via Insert > Module). It will not show up if it is placed in a worksheet module, 'ThisWorkbook', or if it is formatted as a Function rather than a Sub procedure.
Will merging cells using this macro delete my data?
Yes, standard cell merging behavior applies: only the value in the upper-leftmost cell of the matched range is retained, while the duplicates underneath it are discarded. Always ensure you have a backup of your data before running merging macros.
Can I modify the macro to run on a specific worksheet instead of the active selection?
Yes, you can change 'Set rng = Application.Selection' to define a specific range, such as 'Set rng = Worksheets("Sheet1").Range("A1:A100")'. This will allow the macro to target that exact range regardless of what you currently have selected.
