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
Application.DisplayAlerts = True
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 ----------
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
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
9 Range(("A" & jA), ("A" & iA)).Select
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
20 Range(("B" & jB), ("B" & iB)).Select
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
31 Range(("C" & jC), ("C" & iC)).Select
33 End If
34 Next iC
35 Application.DisplayAlerts = True