VBA Excel - 在下面插入具有相同格式的行，包括边框和框架

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

提问by derMax

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?

问题是：此代码仅部分传输格式。它确实为新行使用相同的背景颜色，但它不使用单元格的边框/框架。我怎样才能做到这一点？

回答by Eddy

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

最简单的选择是使用 Excel 复制/粘贴。

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

回答by Donald

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,

谢谢，

回答by richardtallent

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.

插入一行时，无论复制原点如何，只要插入位置的上下边框相同，Excel 只会在插入的单元格上放置垂直边框。

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 conditionalformatting to establish the border (with a Formula of "=TRUE"). The conditional formatting willbe 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 allof 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).

1. 确保垂直边框在插入位置上方和下方具有相同的粗细、颜色和图案，以便 Excel 将它们复制到您的新行中。（这是“当我这样做时会很痛”，“停止这样做！”的答案。）

2. 使用条件格式建立边界（公式为“=TRUE”）。条件格式将被复制到新行，因此您仍然以边框结束。注意事项：

   • 条件格式边框仅限于细线。
   • 最适合边框相对一致的工作表，因此您不必创建一堆条件格式规则。

3. 插入行后，在 VBA 中设置插入行的边框。在范围上设置边框比复制和粘贴所有格式来获得边框要快得多（假设您提前知道边框应该是什么，或者可以从上面的行中对其进行采样而不会损失性能）。

回答by Ditto

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

（我把 ClearContents 放在那里是因为你表示你想要格式，我假设你不想要数据;））