VBA Excel - Insert row below with same format including borders and frames

Question

I want to build a macro that inserts a row below the selected cell with the same format. This is the code I have so far:

Public Sub insertRowBelow()
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
End Sub

The problem is: This code only partially transfers the format. It does use the same background color for the new row, but it does not use the borders/frames for the cells. How can I achieve that?

Answer 1

The easiest option is to make use of the Excel copy/paste.

Public Sub insertRowBelow()
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

Answer 2

Private Sub cmdInsertRow_Click()

    Dim lRow As Long
    Dim lRsp As Long
    On Error Resume Next

    lRow = Selection.Row()
    lRsp = MsgBox("Insert New row above " & lRow & "?", _
            vbQuestion + vbYesNo)
    If lRsp <> vbYes Then Exit Sub

    Rows(lRow).Select
    Selection.Copy
    Rows(lRow + 1).Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False

   'Paste formulas and conditional formatting in new row created
    Rows(lRow).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone

End Sub

This is what I use. Tested and working,

Thanks,

Answer 3

When inserting a row, regardless of the CopyOrigin, Excel will only put vertical borders on the inserted cells if the borders above and below the insert position are the same.

I'm running into a similar (but rotated) situation with inserting columns, but Copy/Paste is too slow for my workbook (tens of thousands of rows, many columns, and complex formatting).

I've found three workarounds that don't require copying the formatting from the source row:

1. Ensure the vertical borders are the same weight, color, and pattern above and below the insert position so Excel will replicate them in your new row. (This is the "It hurts when I do this," "Stop doing that!" answer.)

2. Use conditional formatting to establish the border (with a Formula of "=TRUE"). The conditional formatting will be copied to the new row, so you still end up with a border.Caveats:

   • Conditional formatting borders are limited to the thin-weight lines.
   • Works best for sheets where borders are relatively consistent so you don't have to create a bunch of conditional formatting rules.

3. Set the border on the inserted row in VBA after inserting the row. Setting a border on a range is much faster than copying and pasting all of the formatting just to get a border (assuming you know ahead of time what the border should be or can sample it from the row above without losing performance).

Answer 4

well, using the Macro record, and doing it manually, I ended up with this code .. which seems to work .. (although it's not a one liner like yours ;)

lrow = Selection.Row()
Rows(lrow).Select
Selection.Copy
Rows(lrow + 1).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Selection.ClearContents

(I put the ClearContents in there because you indicated you wanted format, and I'm assuming you didn't want the data ;) )