A "cleverer" cell merge

[caffeine_demon]

HI,

Is there any way of getting excel to merge the cells I've selected into sets of 1 column by 4 rows?

eg select a1 to d16 and merge a1 to a4, a5 to a8, a9 to a12, a13 to a16, b1 to b4 and so on...

 

[Misca]

It might not be what I'd call "clever" but this little macro I wrote should do what you're after.

Sub GroupMergeSelection()
Dim CellsTall As Integer
Dim CellsWide As Integer
Dim c As Range
Dim Rng As Range
 
'If selection size is not a multiple of the following values,
'the missing cells will be added to the merged cells
CellsTall = 4     'Height of each merged range
CellsWide = 1   'Width of each merged range

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False  'Overwrites possible existing values without asking
End With
For Each c In Selection
    If Not c.MergeCells = True Then 'if the cell is not already merged
        Set Rng = c.Resize(CellsTall, CellsWide)
        With Rng
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = True
        End With
    End If
Next c
Application.DisplayAlerts = True
End Sub

Try it with an empty workbook first. Pay attention to the size of your selection: if the selection is smaller than the range required the macro will add the missing cells to the merged ranges without asking. Also make sure there are no values in the cells to be merged: If there are, the macro will only keep the values in the top left cells of each merged range.