Merging cells where data is equivalent 1

[Stoffel24]

Hi
I have no VBA experience and was hoping someone might be able to help me here. I have an excel spreadsheet with a lot of data. What I would like to do is in row 2, starting at column C, I want to go across and find cells where the cell contents in adjacent cells is the same. If it is the same, the cells should be merged and centred.

ie 2 | 2 | 2 | 3 | 4 | 4 | 5 | 5 | 5

becomes

2 | 3 | 4 | 5
I only need to do this with row 2. However, I need to do this for all sheets (there are usually about 4 sheets.)

I hope someone can give me a hand.

Thanks a lot

 

[xlbo]

It's a bit ugly but it does the job:
Sub Merger()
Application.DisplayAlerts = False
For Each sheet In ActiveWorkbook.Sheets
sheet.Select
i = 3
For a = 3 To Range("IV2&quot.End(xlToLeft).Column
cVal = Range(Cells(2, a), Cells(2, a)).Value
For i = a + 1 To Range("IV2&quot.End(xlToLeft).Column
nVal = Range(Cells(2, i), Cells(2, i)).Value
If nVal = cVal Then
Else

Exit For
End If
Next i
Range(Cells(2, a), Cells(2, i - 1)).Select
With Selection
.HorizontalAlignment = xlCenter

.MergeCells = True
End With
a = i - 1
Next a
Next
Application.DisplayAlerts = True
End Sub

 

[Stoffel24]

Many thanks xlbo. I was getting myself nicely frustrated trying to work that one out.