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

Question

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

That should be possible so can you make an attempt or if you have done so, edit your question and include your code then explain what is not working with it? — Raymond Wu, Nov 25 '21 at 09:41
Do you still need the formula in the merged cell or just the value is good enough? — Raymond Wu, Nov 25 '21 at 09:54

score 0 · Accepted Answer · answered Nov 25 '21 at 10:10

0

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

answered Nov 25 '21 at 10:10

Raymond Wu

3,357
2
7
20

OMG!!! thank you so much!!! This is exactly what I've been looking for!!! Been spending few days trying to get this code to work and you solve it within 30 mins!!! – Nov 25 '21 at 10:43
If it helps - I recommend you to read up on [how to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). While your requirement means that the use of `Selection` is required, it is best practice that the rest of your code do not rely on `Selection` / `ActiveCell` as it not reliable (not to mention difficult to keep up with "where is my cell now" situation) @DennisChang – Raymond Wu Nov 25 '21 at 10:48
Forgot to mention this - if an answer submitted to your question resolves your issue, you should acknowledge and accept it by clicking the tick beside the question (if there are multiple answers submitted, you can only accept one). @DennisChang – Raymond Wu Nov 25 '21 at 14:04
Hi Raymond, what if I would like to further add "Thick Bottom Border" from column A to Column M into the existing Macro. Is it possible to do so? – Nov 29 '21 at 08:14
@DennisChang Yes it's possible, try it out and ask a new question if you are stuck. [Range.Borders property](https://learn.microsoft.com/en-us/office/vba/api/excel.range.borders) documentation – Raymond Wu Nov 29 '21 at 08:16

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

1 Answers1