excel vba 插入带格式的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8184373/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
excel vba insert row with formatting
提问by user366121
I have a macro which inserts a number of rows depending on user input in Excel 2007. Everything works but I still have a minor issue. I want to copy the complete formatting from the row above. It only works for some cells in the row.
我有一个宏，它根据 Excel 2007 中的用户输入插入多行。一切正常，但我仍然有一个小问题。我想从上面的行复制完整的格式。它仅适用于行中的某些单元格。
Here is the code for insertion:
下面是插入代码：
Rows("B:B").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Is it possible to change it?
有没有可能改变它？
best, US
最好的，美国
回答by aevanko
I am not sure exactly how you are inserting your rows, but if you insert the row below an existing one, it will by default take on the formatting of the row above it, at least it does when you use this type of syntax:
我不确定您是如何插入行的，但是如果您在现有行下方插入行，默认情况下它将采用其上方行的格式，至少在您使用这种类型的语法时是这样：
Range("B2").EntireRow.Offset(1, 0).Insert
In this example, it will insert a row below B2 and the format (say, B2's row is highlighted yellow) will be yellow as well. It might have to do with the fact that this type of inserting specifies exactly under which row to insert.
在此示例中，它将在 B2 下方插入一行，并且格式（例如，B2 的行以黄色突出显示）也将为黄色。这可能与这种类型的插入准确指定要插入的行的事实有关。
回答by Dam Gal
For those that don't want to inherit formatting from parent range use .cells().
.rows(2).insertwill inherit formatting while .cells(2).insertwill not.
对于那些不想从父范围继承格式的人，请使用.cells().
.rows(2).insert将继承格式，而.cells(2).insert不会。
回答by user366121
The answer is the first comment.
答案是第一个评论。
New code:
新代码：
Rows(CStr(InsRowNumber - 1) & ":" & CStr(InsRowNumber - 1)).Copy
Rows(CStr(InsRowNumber) & ":" & CStr(InsRowNumber)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
回答by nagma
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A4")
Do
Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
Set r = Cells(r.Row + j + 1, 1)
If r.Offset(1, 0) = "" Then Exit Do Loop
