Tag: Merge Cells with VBA

At Zenith Infotech, the HR department just released a new Internal Job Posting (IJP) list. Employees were buzzing with excitement — promotions, new roles, career growth! But when the Excel sheet was shared across departments, it looked… messy.

Here’s what it looked like:

Department	Name	Role
Sales	Radhika Rao	Executive
Sales	Nikhil Jain	Associate
Sales	Suresh Mehta	Manager
IT	Neha Sinha	Developer
IT	Ankit Verma	Tester

Managers wanted a clean sheet — they wanted “Sales” and “IT” to appear merged across rows for easy viewing. That’s when Priya, a young MIS executive, was called in.

HR Head (Meena): “Priya, can we make this IJP sheet look more polished — like grouping departments by merged cells?”

Priya: “Absolutely, Ma’am. Excel loves a challenge!”

---

🧪 The Goal: Merge Adjacent Rows with the Same Data in Excel

We want the “Sales” cells to be merged down into one, the same with “IT”, but only if they are next to each other.

---

✅ Step-by-Step: Manually Merging Adjacent Cells with Same Values

1. Sort the Data by the column you want to merge (e.g., Department).
   • Go to Data → Sort
2. Select the range (e.g., A2:A6)
3. Manually highlight the adjacent cells with the same value (like all “Sales”)
4. Go to Home → Click Merge & Center
5. Repeat for other repeated values.

🎯 This is easy and clean for small data sets, like Priya’s IJP sheet.

---

⚙️ But Priya Wanted Automation…

She used a VBA Macro to speed it up for 100s of rows.

🧾 VBA Code to Merge Adjacent Cells in Column A:

vbaCopyEditSub MergeSameCells()
    Dim ws As Worksheet
    Dim i As Long, lastRow As Long
    Set ws = ActiveSheet
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    i = 2
    
    While i <= lastRow
        Dim startRow As Long: startRow = i
        Do While i < lastRow And ws.Cells(i, 1).Value = ws.Cells(i + 1, 1).Value
            i = i + 1
        Loop
        
        If i > startRow Then
            ws.Range(ws.Cells(startRow, 1), ws.Cells(i, 1)).Merge
            ws.Cells(startRow, 1).HorizontalAlignment = xlCenter
            ws.Cells(startRow, 1).VerticalAlignment = xlCenter
        End If
        
        i = i + 1
    Wend
End Sub

🔹 How to Use:

1. Press Alt + F11 to open VBA editor
2. Insert → Module → Paste the code
3. Press F5 to run
4. It will merge adjacent cells in Column A with same value.

---

🧠 Result?

Department (Merged)	Name	Role
Sales	Radhika Rao	Executive
	Nikhil Jain	Associate
	Suresh Mehta	Manager
IT	Neha Sinha	Developer
	Ankit Verma	Tester

Priya printed it and handed it to Meena.

Meena (smiling): “This looks like something made for the CEO’s desk. Great work, Priya!”

---

🧰 Bonus (No VBA): Use Excel Power Query

1. Select data → Go to Data → From Table/Range
2. Use Power Query to group by the column
3. Expand details in the remaining columns
4. Load data back to Excel
   (Note: This doesn’t “merge” visually, but summarizes like a report)

---

📝 Summary:

• For small data → Use manual “Merge & Center”
• For many rows → Use a VBA Macro
• For structured grouping → Try Power Query

---