On Thu, 19 Apr 2012 10:51:34 +0000, Vasanth wrote:


Hi

Below is my requirement

I have cells in a excel in the below format

AAA 12323
AAA 56565
AAA 77789
BBB 12
BBB 13
DDD 142
CCC 121
CCC 13


I need to merge the cell of column A until the value is same . My output
should be


12323
AAA 56565
77789

12
BBB 13

DDD 142

CCC 121
13

Please let me know if this can be achieved through some macros or
forula

Thanks
Vasanth


This Macro assumes your cells to be merged are in column A, starting in A1. If they start someplace else, change the initial
Set r = ...
statement to reflect the proper column.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

=============================================
Option Explicit
Sub MergeSame()
Dim r As Range, c As Range
Dim i As Long, j As Long
Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp))

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For i = 1 To r.Count
Set c = r(i)
j = 0
Do Until c < c.Offset(rowoffset:=1)
Set c = c(2)
j = j + 1
Loop
With Range(r(i), c)
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
i = i + j
Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
=====================================