excel merge cells keep data code example

Example 1: excel merge cells

Sub MergeCells()
'
' MergeCells Macro
'
    Dim var As String
    
    rg = Selection.Address    ' Current selected range e.g. "F2:F5"
    Application.DisplayAlerts = False ' No popup when we will merge the cells at the end
    
    var = ""
    ' Selection is the current cell(s) selected e.g. "F2:F5", cell is F2, F3, ..., F5
    For Each cell In Selection
        var = var & vbCrLf & cell.Value ' Concatenate with newline in between
        
    Next cell
    
    ' Merge original selection, and update its value
    Range(rg).Merge
    Range(rg).Value = var
'
' MergeCells Macro
' https://stackoverflow.com/questions/4080741/get-user-selected-range
' https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa203726(v=office.11)?redirectedfrom=MSDN#concatenate-columns-of-data
' https://www.automateexcel.com/vba/cell-value-get-set/#Assign_Cell_Value_to_Variable
' https://stackoverflow.com/questions/26008268/get-the-value-of-the-cell-to-the-right-in-vba
' https://www.ozgrid.com/forum/index.php?thread/66522-supress-merge-cells-warning-message/
End Sub

Example 2: excel merge cells

Sub MergeRightCells()
'
' Macro2 Macro
' https://stackoverflow.com/questions/4080741/get-user-selected-range
' https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa203726(v=office.11)?redirectedfrom=MSDN#concatenate-columns-of-data
' https://www.automateexcel.com/vba/cell-value-get-set/#Assign_Cell_Value_to_Variable
' https://stackoverflow.com/questions/26008268/get-the-value-of-the-cell-to-the-right-in-vba
' https://www.ozgrid.com/forum/index.php?thread/66522-supress-merge-cells-warning-message/
'
    Dim var As String
      
    var = ""
    
    ' Selection is the current cell(s) selected e.g. "F2:F5"
    For Each cell In Selection
        ' cell.Offset(, 1) takes the cell on the right e.g. G2, G3, G4, G5
        var = var & vbCrLf & cell.Offset(, 1).Value
        
    Next cell
    
    ' Take the concatenating values and put it back in the original selection
    ActiveCell.Value = var

'
End Sub