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:
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
Further reading:
Merge Cells without Losing Data [Quick Tip]