You are here:

Excel/Automatically Merging Cells Using Excel 2010


Is there a way to create a macro or VBA code that would go into my spreadsheet and automatically merge cells with the same "Community" and same "Project Initiative" column entries?

See attached sample. Columns A2-B26 is what I want the end result to look like. The rest is the raw/output data.

ANSWER: Hi Jessica,

The screenshot you sent is not clear enough. However, below is my understanding of your requirement.
1. Check for all rows where column A value = Column B value.
2. If the values are equal, then merge the two cells.

Please note that the value in column A will become the final value in the merged cell.

Based on the above, I have prepared this macro, can you try using it and confirm if it works fine:

Private Sub CommandButton1_click()
Application.DisplayAlerts = False
For chksame = 1 To 30
If Cells (chksame,1).Value = Cells (chksame,2).Value Then Range (("A" & chksame),("B" & chksame)).MergeCells = True
Next chksame
Application.DisplayAlerts = True
End Sub

I have created this macro for a range of 30 rows, but you can increase it by changing the value of chksame = 1 to xxxx.

Hope this helps,

---------- FOLLOW-UP ----------

Raw Data
Raw Data  

Final Product
Final Product  
QUESTION: Hi Gulshan,
Close to what I need... I need "If Column A2 Value = A3, A4, A5:A150 then merge all cells where A2 Value matches"
I also need this macro to check Column B2 and merge any like Values and Column C2 for like Values and merge. I attached a sample of raw data and sample of final product.

Or does it makes sense if I say... Merge All Like Values within Column A, B, and C. With Column A being the Primary ID

Hi Jessica,

Please see the below macro. The basic assumption I've taken is that the values in column A,B and C are sorted, otherwise, the cells will merge only where they are same.
Hope this helps:

Private Sub CommandButton1_Click()
Dim totcountA, totcountB, totcountC
1 Application.DisplayAlerts = False
2 totcountA = WorksheetFunction.CountA(Worksheets("Sheet1").Range("A:A"))
3 For iA = 1 To totcountA
4 jA = iA
5 If Cells(iA, 1).Value = Cells((iA + 1), 1).Value Then
6 iA = iA + 1
7 GoTo 5
8 Else
9 Range(("A" & jA), ("A" & iA)).Select
10 Selection.Merge
11 End If
12 Next iA
13 totcountB = WorksheetFunction.CountA(Worksheets("Sheet1").Range("B:B"))
14 For iB = 1 To totcountB
15 jB = iB
16 If Cells(iB, 2).Value = Cells((iB + 1), 2).Value Then
17 iB = iB + 1
18 GoTo 16
19 Else
20 Range(("B" & jB), ("B" & iB)).Select
21 Selection.Merge
22 End If
23 Next iB
24 totcountC = WorksheetFunction.CountA(Worksheets("Sheet1").Range("C:C"))
25 For iC = 1 To totcountC
26 jC = iC
27 If Cells(iC, 3).Value = Cells((iC + 1), 3).Value Then
28 iC = iC + 1
29 GoTo 27
30 Else
31 Range(("C" & jC), ("C" & iC)).Select
32 Selection.Merge
33 End If
34 Next iC
35 Application.DisplayAlerts = True

End Sub

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 All rights reserved.