向给定范围添加偏移行。excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17789077/
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
Adding an Offset Row to a Given Range. Excel VBA
提问by user1305569
I have a variable which at the beginning is set to a given range.
我有一个变量，它在开始时设置为给定的范围。
I want to have a loop statement that would take the next row down from the end of the given range and add it to that range.
我想要一个循环语句，它将从给定范围的末尾向下取下一行并将其添加到该范围。
ie:
IE：
myRows = Range(1:10)
For Each cell in myRows
If cell.Value > 2048 Then
myRows = myRows + myRows.Offset(1, 0) ---This is where i need help---
Basically how do i auto increment the range each time the loop runs.
基本上我如何在每次循环运行时自动增加范围。
Edit:
编辑：
Also how would I Add to the front of the range. As well as Take away from the back of the range.
另外我将如何添加到范围的前面。以及从范围的后面带走。
ie
IE
Range(1:10) is unhidden Range(11:20) is hidden
Range(1:10) 未隐藏 Range(11:20) 已隐藏
I want to add one to the unhidden range which would take away from the hidden range so its:
我想在未隐藏的范围内添加一个，这会从隐藏的范围中移除，因此它的：
Range(1:11) is unhidden Range(12:20) is hidden
Range(1:11) 未隐藏 Range(12:20) 隐藏
回答by David Zemens
You have myRowsas a Variantdata type. You need to declare it as a Rangeobject.
你有myRows一个Variant数据类型。您需要将其声明为Range对象。
Dim myRows as Range
Dim myRows as Range
Then you would need to use the Setkeyword to assign a Rangeobject).
然后你需要使用Set关键字来分配一个Range对象）。
Set myRows = Range("1:10")
Set myRows = Range("1:10")
Then, use the range .Resizemethod:
然后，使用范围.Resize方法：
Set myRows = myRows.Resize(myRows.Rows.Count+1, myRows.Columns.Count)
Set myRows = myRows.Resize(myRows.Rows.Count+1, myRows.Columns.Count)
Otherwise, if you need to maintain myRowsas type Variant, let me know and I can re-work this.
否则，如果您需要维护myRows为 Variant 类型，请告诉我，我可以重新处理此问题。
回答by LS_???
You may also use
你也可以使用
Set myRows = Union(myRows, myRows.Offset(1, 0))
EDIT:
编辑：
You asked for it!
你自找的！
To remove one row from generic range
从通用范围中删除一行
Set myRows = RemoveRowFromRange(myRows, 10)
...
Function RemoveRowFromRange(ByVal Range As Range, row_number As Long) As Range
With Range.Worksheet
Select Case row_number
Case 1
Set Range = Intersect(Range, .Range(.Rows(2), .Rows(.Rows.Count)))
Case .Rows.Count
Set Range = Intersect(Range, .Range(.Rows(1), .Rows(.Rows.Count - 1)))
Case Else
Set Range = Union(Intersect(Range, .Range(.Rows(1), .Rows(row_number - 1))), Intersect(Range, .Range(.Rows(row_number + 1), .Rows(.Rows.Count))))
End Select
End With
Set RemoveRowFromRange = Range
End Function
