Snasui Excel Forum

Code: Select all

Sub MergeSameCell()
'Updateby20171127
Sheet9.Select
ActiveSheet.Unprotect
Dim Rng As Range, xCell As Range
Dim xRows As Integer
xTitleId = "KutoolsforExcel"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set WorkRng = Range("A5:B500,E5:G500")
xRows = Range("A" & Rows.Count).End(xlUp).Row
For Each Rng In WorkRng.Columns
    For i = 1 To xRows - 1
        For j = i + 1 To xRows
            If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
                Exit For
            End If
        Next
        WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
        WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).HorizontalAlignment = xlCenter
        WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).VerticalAlignment = xlCenter
        i = j - 1
    Next
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub