VBA & Macro Problems

How to Merge Excel Cells with Duplicate Order Numbers and Dates using VBA

Maira Mehtab Oct 19, 2026 870 views

Question details

The user needs to automatically merge adjacent cells in Excel when consecutive rows share the exact same Posting Date and Order Number.

How to Merge Excel Cells with Duplicate Order Numbers and Dates

Product: Excel
Device & OS: not provided
Scenario: Organizing and formatting a dataset where grouped records contain matching dates and order numbers across multiple rows, requiring a cleaner, consolidated visual layout.
Observed behavior: Duplicate values are populated across consecutive rows, causing visual clutter. A manual merge is too time-consuming, necessitating an automated VBA script to evaluate and merge identical neighboring cells.

Before you start

Before running any VBA macro, ensure your data is sorted by Posting Date and Order Number so that all duplicates are grouped adjacently. Since merging cells deletes the data in the bottommost cells and macro actions cannot be undone, please save a backup copy of your workbook first.

Solution 1Recommended

Use a VBA Macro to Merge Duplicate Adjacent Cells

Running a bottom-up VBA script is the fastest way to evaluate neighboring rows and automatically merge cells with matching criteria.

This VBA macro processes the worksheet from the bottom upward. For each row, it compares the Posting Date and Order Number with the row immediately above it. If both match, it automatically merges the corresponding cells in those columns.

While merging improves the visual presentation of your data, keep in mind that merged cells can complicate future sorting, filtering, and data analysis. If you plan to heavily manipulate the data later, consider using grouping, conditional formatting, or a PivotTable instead.

Open the VBA Editor

In your open Excel workbook, press 'Alt + F11' on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) editor.

Insert a New Module

Click on 'Insert' in the top menu bar, then select 'Module' from the dropdown to create a blank script window.

Paste the Macro Code

Copy and paste the following code into the module window: Sub Merge() Application.DisplayAlerts = False RowNumber = Range("A65536").End(xlUp).Row For i = RowNumber To 2 Step -1 If Cells(i, 2).Value = Cells(i - 1, 2).Value Then If Cells(i, 1).Value = Cells(i - 1, 1).Value Then Range(Cells(i - 1, 2), Cells(i, 2)).Merge Range(Cells(i - 1, 1), Cells(i, 1)).Merge End If End If Next i Application.DisplayAlerts = True End Sub

Run the Macro

Close the VBA editor to return to your worksheet. Press 'Alt + F8' to open the Macro dialog box, select 'Merge' from the list, and click 'Run'. Your duplicate adjacent cells will now be merged.

Use a VBA Macro to Merge Duplicate Adjacent Cells

Why loop from the bottom upwards?: The code uses 'Step -1' to process rows from the bottom up. If the macro processed from the top down, merging rows would shift the row index dynamically, causing the loop to skip rows and miss duplicate values.

Automate Data Processing with WPS Office

Easily Manage Macros and Spreadsheets in WPS Office

WPS Spreadsheet fully supports VBA macros, allowing you to run scripts to merge cells, format data, and automate repetitive tasks just like in Microsoft Excel. It offers an intuitive interface, making complex data processing seamless and efficient.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the duplicate order numbers and dates.
2. Access the Developer Tools: Navigate to the 'Tools' tab on the ribbon and click on 'Developer' to access the VBA macro features.
3. Run the Merge Macro: Open the VBA Editor, paste the provided macro code into a new module, and execute it to merge your duplicate cells instantly.
4. Save as a Macro-Enabled Workbook: Go to 'Menu' > 'Save As' and choose the '.xlsm' format to ensure your macro is preserved for future use.

100% compatible with Microsoft Excel (.xlsx, .xlsm) formats and VBA macro scripts.Lightweight architecture ensures fast performance even when running macros on large datasets.Includes powerful built-in tools like PivotTables and conditional formatting as robust alternatives to merging cells.

microsoft office alternative - wps office

Frequently Asked Questions

Why do merged cells cause issues with sorting and filtering?

Merged cells combine multiple rows into a single block, disrupting the standard row-by-row structure of the spreadsheet. When you attempt to sort or filter, the software expects uniformly sized rows, triggering an error if merged blocks vary in size.

How can I undo the merged cells if I make a mistake?

You can unmerge them by selecting the affected area and clicking the 'Merge & Center' button on the Home tab. However, because merging via macro deletes the underlying data in the lower rows, unmerging will leave blank cells. You must restore your data from the backup you created before running the script.

Can I modify the macro to evaluate different columns?

Yes. In the macro code, 'Cells(i, 1)' refers to Column A, and 'Cells(i, 2)' refers to Column B. You can simply change the numbers to correspond to the columns you want to evaluate (for example, use '3' for Column C).

Are there alternatives to merging cells for duplicate data?

Yes. Instead of physically merging cells, you can use Conditional Formatting to make duplicate text white (invisible), use the 'Center Across Selection' alignment option, or summarize your data using a PivotTable. These methods achieve a cleaner look without breaking sorting functionality.

Excel & Data Problems