Merging Cells

[ygsunilkumar]

Hi, I need to macro wherein it will merge the cells.

Details:
1. Merging should happen based on the ID (column D)
2. Rest of the columns that is (Column A, B, C, E, F, G, H, I) should be merged if the data is repeating.
3. Column A, B, C will be same for all the rows, these cells should be merged when the ID changes.
4. Sample Excel Sheet has been attached for reference. Please refer "Raw Data" and "Desired Output" sheet.

Please do let me know need any further information.

Thanks in Advance.

[Bob Phillips]

Dim i As Long, j As Long
Dim LastRow As Long
Dim StartRow As Long

Application.DisplayAlerts = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 2
For i = 3 To LastRow + 1

Do While .Cells(i, "A").Value2 = .Cells(i - 1, "A").Value2 And _
.Cells(i, "B").Value2 = .Cells(i - 1, "B").Value2 And _
.Cells(i, "C").Value2 = .Cells(i - 1, "C").Value2 And _
.Cells(i, "D").Value2 = .Cells(i - 1, "D").Value2 And _
.Cells(i, "F").Value2 = .Cells(i - 1, "F").Value2

i = i + 1
Loop

For j = 1 To 4

.Cells(StartRow, j).Resize(i - StartRow).MergeCells = True
.Cells(StartRow, j).Resize(i - StartRow).VerticalAlignment = xlCenter
Next j

For j = 6 To 7

.Cells(StartRow, j).Resize(i - StartRow).MergeCells = True
.Cells(StartRow, j).Resize(i - StartRow).VerticalAlignment = xlCenter
Next j

StartRow = i
Next i
End With

Application.DisplayAlerts = True

[ygsunilkumar]

Thanks for the code. Its working correctly where entire rows are repeating equally but please see the attached sheet wherein for the ID = 10 it is not merged but I expect to merge the cells for all columns except "Column F" because it contains all distinct values.

And it should also include "Column H and Column I" for merging.

Thanks in Advance.

[Bob Phillips]

Thanks for the code. Its working correctly where entire rows are repeating equally but please see the attached sheet wherein for the ID = 10 it is not merged but I expect to merge the cells for all columns except "Column F" because it contains all distinct values.

So you want to destroy those values?


And it should also include "Column H and Column I" for merging.

Your previous example showed exactly the opposite, H and I were preserved.

[ygsunilkumar]

So you want to destroy those values?: No I don't want to destroy those values instead it has to merge the cells except for "column F".

Previous example H and I were not preserved. Please go through the attachment of previous thread.

Thanks.

[ygsunilkumar]

Please any one help, I required it very urgently. Thanks

[parttime_guy]

Dear XLD & Sunil,

I have amended your code to match Sunil's requirements - kindly review attached file.



Best regards

[ygsunilkumar]

Thanks a lot for your code parttime_guy. Its working fine.

I have another requirement wherein cells should not merge for Column E and F even though it contains duplicate values. Only it should merge for the Column A, B, C, D and G.


I tried but could not able to get the output. Can you please help? Please find the information in the attachment.

Thanks in Advance.

[parttime_guy]

Hi Sunil,

I have solved your new query for Column A, B, C & D.

I am a little confused with Column F & G.

In the Original Flap Column F has information of numbers 4 – 10
While your Desired Output Flap has information which is not in the original flap – kindly enlighten me.

Similarly, in the original flap Column G has information (eg P003… etc)
While your desired output flap has information which is not in the original flap (eg 3, 7, 5, 8) – how do you manage that ? Kindly enlighten me on this one too.

Please review attached file.

Best regards

[ygsunilkumar]

Hi parttime_guy,

Thanks for you code.

Sorry for the confused values of Column F & G.

I have made necessary changes. Please find the attached file.

Merging should be done for the Column A, B, C, D and G. Right now merging is not happening for column G.

Thanks in Advance.

[parttime_guy]

Hi Sunil,

Try this new code.... hope this helps



BR