How to Merge and Sum to a new column in Excel VBA-CodePudding

I have a table below that I would like to use Macro (or any method) to obtain the following result:

Original Table

Expected Result

The problem I faced is Column A will not be a fixed number of rows. I would like to highlight the number of rows in column A and automatically sum and merge into column B. Is it possible to do this with VBA / Macro in Excel?

Another Example: I have 4 Cells in Column A and would like to highlight the 4 Cells then trigger the Macro. It will automatically goes to column B and merged 4 cells in Column B (not column A) and show the SUM of 4 highlighted cells.

The number of cells in column A will not be fixed and depends on the data. This is why I trying to create a macro instead of merge and sum manually.

I have the code that manage to merge highlighted Rows in Column A and merge in Column B. However i have no idea how to proceed with the SUM.


    Sub Macro6()
'
' Macro6 Macro
'
' Keyboard Shortcut: Ctrl l
'
selection.Offset(0, 1).Select
selection.Merge
    With selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = Falsex
        .ReadingOrder = xlContext
    End With
    ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:RC[-1])"
End Sub

CodePudding user response：

This should work:

Option Explicit

Sub Macro6()
'
' Macro6 Macro
'
' Keyboard Shortcut: Ctrl l
    Dim mergeCells As Range
    Set mergeCells = Selection.Offset(, 1)
    
    With mergeCells
        .Merge
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .Formula = "=SUM(" & Selection.Address & ")"
    End With
End Sub