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

User · Accepted Answer

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

User · Answer

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    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   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   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

User · Answer

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    amp  lRow  amp                     vbQuestion   vbYesNo      If lRsp  lt  gt  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

User · Answer

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

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

Examples related to excel

Examples related to vba