Excel-VBA：在下方插入行并获取新行作为范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19264755/
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 below and get new row as Range
提问by beginner_
How can I insert a new row below a range and then get a reference to that new row so I can populate it?
如何在范围下方插入新行，然后获取对该新行的引用以便填充它？
Dim newRow As Range
newRow = row.Offset(1).EntireRow.Insert
results in an object variable or width block variable not set error but the new row is inserted. I can't however seem to get a reference to it.
导致对象变量或宽度块变量未设置错误，但插入了新行。但是，我似乎无法引用它。
A second question is that in above could I'm iterating over range and inserting rows into that range. Will the insert affect the iteration?
第二个问题是，在上面我可以遍历范围并将行插入该范围。插入会影响迭代吗？
采纳答案by beginner_
OMG. Stupid VBA syntax. ;)
我的天啊。愚蠢的 VBA 语法。;)
Set newRow = Rows(rowNumber + 1 & ":" & rowNumber + 1)
works. the important part beign the Set keyword.
作品。重要的部分是 Set 关键字。
回答by jmstoker
My prior statement was incorrect because I misunderstood what you were trying to do. Below is an example of looping through a range and upon satisfying a condition, a row will be inserted below the current row in the loop. A boolean is needed to then skip over the inserted row.
我之前的陈述是不正确的，因为我误解了你想要做什么。下面是一个循环遍历范围的示例，在满足条件时，将在循环中的当前行下方插入一行。然后需要一个布尔值来跳过插入的行。
Sub InsertAfterLastRow()
Dim Rng As Range
Set Rng = Range("A1:B5") 'Arbritrary
Dim LastRow As Long
Dim InsertRow As Long
Dim Inserted As Boolean
Dim NewRow As Range
Inserted = False
For Each Row In Rng.Rows
If Inserted = False Then
If Row.Cells(1, 1).Value = "Yes" Then
Rows(Row.Row + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Set NewRow = Range(Row.Row + 1 & ":" & Row.Row + 1)
NewRow.Cells(1, 1).Value = "Inserted"
Inserted = True
Else
Row.Cells(1, 1).Value = "No"
End If
Else
'Avoid a double insert, skipping a row
Inserted = False
End If
Next
End Sub
回答by Getafix
I've been battling with a similar issue wanting to add or delete a variable number of rows at a point below a reference range (named range) without affecting the named range. First I determine how many rows to add (or remove) variable holding the number of rows = addR
我一直在与类似的问题作斗争，希望在低于参考范围（命名范围）的点添加或删除可变数量的行而不影响命名范围。首先，我确定要添加（或删除）包含行数 = addR 的变量的行数
addR = 2 ' you'd use some logic to derive the value of addR
Rows(Range("OutputBlock").Row + 1).EntireRow.Resize(addR).insert
Output block being my named range where I want to add the rows.
addR = 2 ' you'd use some logic to derive the value of addR
Rows(Range("OutputBlock").Row + 1).EntireRow.Resize(addR).insert
输出块是我要添加行的命名范围。
