vba 在 For 循环中设置范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7223507/
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
Setting Range in For Loop
提问by Ali
I am trying to set the range in For loop. My code works fine when I do this:
我正在尝试在 For 循环中设置范围。当我这样做时，我的代码工作正常：
For Each i in Range("A1":"A5")
'Some process code
Next i
But I do not get the same results when I do this:
但是当我这样做时，我没有得到相同的结果：
For Each i in Range("A1").End(xlDown)
'Some Process
Next i
Arent the two codes equivalent? What changes should I make to the second one that it perfoms the same way as the first one but doesn't make me hardcode the Range in the code?
这两个代码是不是等价的？我应该对第二个进行哪些更改，使其与第一个的执行方式相同，但不会让我在代码中对 Range 进行硬编码？
回答by adamleerich
The second one you have only gets the last cell in the range, which I believe would me A5 from the first example. Instead, you need to do something like this.
您拥有的第二个单元只能获取范围中的最后一个单元格，我相信第一个示例中的 A5 单元格。相反，你需要做这样的事情。
I structured this like a small test so you can see the first option, the corrected second, and an example of how I would prefer to do this.
我把它组织成一个小测试，所以你可以看到第一个选项，更正的第二个，以及我更喜欢如何做的一个例子。
Option Explicit
Sub test()
Dim r As Range
Dim x As Range
' Make sure there is stuff in Range("A1:A5")
Range("A1") = 1
Range("A2") = 2
Range("A3") = 3
Range("A4") = 4
Range("A5") = 5
' Your first option
For Each x In Range("A1:A5")
Debug.Print x.Address & ", " & x
Next
' What you need to do to get the full range
For Each x In Range("A1", Range("A1").End(xlDown))
Debug.Print x.Address & ", " & x
Next
' My preferred method
Set r = Range("A1").End(xlDown)
For Each x In Range("A1", r)
Debug.Print x.Address & ", " & x
Next
End Sub
回答by aevanko
The cleanest way to do it would probobly be to store the lastRow number in a variable like so. You can do the concatenation in the for each line:
最干净的方法可能是将 lastRow 编号存储在像这样的变量中。您可以在每一行中进行连接：
Dim cell as range
Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).row
For Each cell In Range("A1:A" & lastRow)
Please note that it makes a difference between using xlUpand xlDown.
请注意，使用xlUp和xlDown有所不同。
- xlUp gives you last cell used in column A (so you start at rows.count)
- XlDown gives you last non-blank cell (you can use range("A1").End(xlDown).Row)
- xlUp 为您提供 A 列中使用的最后一个单元格（因此您从行数开始）
- XlDown 为您提供最后一个非空白单元格（您可以使用range("A1").End(xlDown).Row）
You'll notice a lot of people use "A65536" instead of rows.count, but 65536 is not the limit for some versions of Excel, so it's always better to use rows.count.
您会注意到很多人使用“A65536”而不是rows.count，但65536 不是某些版本的Excel 的限制，因此使用rows.count 总是更好。
