Merge Cells Repeat

[BeaglesBuddy]

Is there a macro that can merge two cells in a column while deleting the value in the 2nd row. Right now when i merge those cells the 2nd value deletes automatically. I would like to automate this for the entire column.
Sorry if this has been asked already. I searched and couldn't find anything that applied with my lack of vb knowledge.

 

[SydneyGeek]

Place this code into a new module (Alt+F11, Insert>Module, Paste, then Alt+Q to return to Excel).
To run the macro select the first cell in the range, click Alt+F8, and double-click the macro name.

Sub MergeLoop()
    Application.DisplayAlerts = False
    Do Until IsEmpty(ActiveCell)
        ActiveCell.Resize(2, 1).Merge
        ActiveCell.Offset(1, 0).Select
    Loop
    Application.DisplayAlerts = True
End Sub

Denis

 

[BeaglesBuddy]

Thanks!

 

[OhioAndrea]

Place this code into a new module (Alt+F11, Insert>Module, Paste, then Alt+Q to return to Excel).
To run the macro select the first cell in the range, click Alt+F8, and double-click the macro name.

Sub MergeLoop()
    Application.DisplayAlerts = False
    Do Until IsEmpty(ActiveCell)
        ActiveCell.Resize(2, 1).Merge
        ActiveCell.Offset(1, 0).Select
    Loop
    Application.DisplayAlerts = True
End Sub

Denis

This code worked perfectly for me! I would like to repeat this code on the next column from the same row that I started it initially (example: I started this macro with A3 as the active cell...I would like it to repeat starting on cell B3). I would like it to repeat for columns A-G (7 columns total - this will remain consistent). Is there a way to do this? I appreciate the help!

 

[SydneyGeek]

Hi

Here is one way to extend the macro:

Sub MultiCol()
    Dim i As Long
    Const RwStart = 3
    
    For i = 1 To 7
        Cells(RwStart, i).Select
        MergeLoop
    Next i
End Sub


Sub MergeLoop()
    Application.DisplayAlerts = False
    Do Until IsEmpty(ActiveCell)
        ActiveCell.Resize(2, 1).Merge
        ActiveCell.Offset(1, 0).Select
    Loop
    Application.DisplayAlerts = True
End Sub

Replace the existing code with the new version above
While on the sheet where you want to merge the cells, press {Alt}+{F8} and run the MultiCol macro

MultiCol runs from columns A to G (that's the 1 to 7 values for i, in the declaration)
It starts at Row 3 (that's the RwStart constant)
To change the range and / or the starting row, adjust those values

Denis

 

[OhioAndrea]

This is perfect, thank you so very very much!!