To apply "Thick Bottom Border" to certain column-CodePudding

I have a table from Column A to Column M where I will input new row of data everyday. I have a Macro which enables me to highlight selected cells in Column I and sum to column J including merge. However, I would like to add "Thick Bottom Border" from Column A to Column M after triggering the Macro. In addition, it would be a better if the Selection Cells will go to Column C 1 row below for faster data input.

Image below for your reference: enter image description here

Expected Result: enter image description here

    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

CodePudding user response：

If you run the macro recorder, and add your border, you should get something along the lines of:

With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With

And now that you know how to add a border, you just need to specify the range.

One way would be to get the last row of your selection. Which can be done by looking at the starting row, the amount of selected rows, and then subtracting one, since we are essentially counting the first row twice.
lrow = Selection.Row Selection.Rows.Count - 1

Since the range in where you want this will always be the same, it's easy enough to hard-code it, by concatenating the Column and the row, using &.

    Sub Macro6()
'
' Macro6 Macro
'
' Keyboard Shortcut: Ctrl l
    Dim lRow as Long     
    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
    lRow = Selection.Row   Selection.Rows.Count - 1
    With Range("A" & lRow, "M" & lRow).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With

End Sub