vba 当给定 Range 对象时，循环遍历单元格范围内的每个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3875415/
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
Loop through each cell in a range of cells when given a Range object
提问by Ben McCormack
Let's say I have the following code:
假设我有以下代码：
Sub TestRangeLoop()
Dim rng As Range
Set rng = Range("A1:A6")
''//Insert code to loop through rng here
End Sub
I want to be able to iterate through a collection of Rangeobjects for each cell specified in rng. Conceptually, I'd like to do it like so:
我想通过集合，以便能够迭代Range在指定每个单元的对象rng。从概念上讲，我想这样做：
For Each rngCell As Range in rng
''//Do something with rngCell
Next
I know I could solve this by parsing rng.Addressand building Rangeobjects manually, but I'm hoping there is a more direct way that doesn't involve string parsing.
我知道我可以通过手动解析rng.Address和构建Range对象来解决这个问题，但我希望有一种不涉及字符串解析的更直接的方法。
回答by Dick Kusleika
Sub LoopRange()
Dim rCell As Range
Dim rRng As Range
Set rRng = Sheet1.Range("A1:A6")
For Each rCell In rRng.Cells
Debug.Print rCell.Address, rCell.Value
Next rCell
End Sub
回答by code4life
You could use Range.Rows, Range.Columnsor Range.Cells. Each of these collections contain Rangeobjects.
您可以使用Range.Rows,Range.Columns或Range.Cells。这些集合中的每一个都包含Range对象。
Here's how you could modify Dick's example so as to work with Rows:
以下是如何修改 Dick 的示例以便使用Rows：
Sub LoopRange()
Dim rCell As Range
Dim rRng As Range
Set rRng = Sheet1.Range("A1:A6")
For Each rCell In rRng.Rows
Debug.Print rCell.Address, rCell.Value
Next rCell
End Sub
And Columns:
并且Columns：
Sub LoopRange()
Dim rCell As Range
Dim rRng As Range
Set rRng = Sheet1.Range("A1:A6")
For Each rCol In rRng.Columns
For Each rCell In rCol.Rows
Debug.Print rCell.Address, rCell.Value
Next rCell
Next rCol
End Sub
回答by Mark Avenius
To make a note on Dick's answer, this is correct, but I would not recommend using a For Each loop. For Each creates a temporary reference to the COM Cell behind the scenes that you do not have access to (that you would need in order to dispose of it).
为了记录 Dick 的回答，这是正确的，但我不建议使用 For Each 循环。For Each 在幕后创建一个对 COM Cell 的临时引用，您无权访问（您需要处理它）。
See the following for more discussion:
有关更多讨论，请参阅以下内容：
How do I properly clean up Excel interop objects?
To illustrate the issue, try the For Each example, close your application, and look at Task Manager. You should see that an instance of Excel is still running (because all objects were not disposed of properly).
为了说明问题，请尝试 For Each 示例，关闭您的应用程序，然后查看任务管理器。您应该看到 Excel 的一个实例仍在运行（因为所有对象都没有正确处理）。
A cleaner way to handle this is to query the spreadsheet using ADO:
一种更简洁的处理方法是使用 ADO 查询电子表格：
回答by Nielsvh
I'm resurrecting the dead here, but because a range can be defined as "A:A", using a for each loop ends up with a potential infinite loop. The solution, as far as I know, is to use the Do Untilloop.
我在这里复活死者，但是因为范围可以定义为“A:A”，所以对每个循环使用 a 最终会导致潜在的无限循环。据我所知，解决方案是使用Do Until循环。
Do Until Selection.Value = ""
Rem Do things here...
Loop
