Merge Data of Excel Cells Using VBA

Home More Videos


Recently somebody wanted to know how to merge cells in MS Excel without losing data. Using the standard feature in Excel of merge and center caused data loss. For example you got the warning as shown in the image below:

merge-cells-ms-excel

However, using a loop (do while) in Excel we were able to merge the cells quickly and easily as shown in the video below:

The complete code for the implementation of the merge process is given here:
Sub MergeAndCenter()
Dim myText As String, mySpace As String
mySpace = " "
Cells(1, 1) = "Name"
Cells(1, 2) = ""
x = 2
Do While Cells(x, 1) <> ""
' Loop as long as the cell is not empty
Cells(x, 1) = Cells(x, 1) & mySpace & Cells(x, 2)
Cells(x, 2) = ""
x = x + 1
Loop
Columns("A:A").EntireColumn.AutoFit
'Code to center the text
Columns("A:A").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    End With
End Sub