Excel VBA - 范围及其子范围的联合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20055984/
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 - Union of Ranges and its sub ranges
提问by cheezsteak
Consider the following vba macro in excel
考虑excel中的以下vba宏
Sub foo()
Dim aRng As Range: Set aRng = ActiveSheet.Range("A1:J1")
Dim bRng As Range: Set bRng = ActiveSheet.Range("A4:J4")
Dim cRng As Range: Set cRng = ActiveSheet.Range("A10:J10")
Dim uRng As Range: Set uRng = Union(aRng, bRng, cRng)
uRng.Style = "Good"
uRng.Cells(2, 1).Style = "Bad"
End sub
The results are: Rows 1 "A1:J1", "A4:J4", "A10:J10"are goodand cell "A2"is bad.
I expected cell "A4"to be bad. "A2" is not in uRng; why would it be returned by uRng.Cells(2,1)?
结果是： Rows 1 "A1:J1", "A4:J4", "A10:J10"are goodand cell "A2"is bad。我预计电池"A4"会坏。“A2”不在uRng；为什么会被退回uRng.Cells(2,1)？
Other oddities: uRng.Rows.Count = 1and uRng.Columns.Count = 10. Am I wrong to expect uRngto be 3x10 range? Or is that undefined because the positions of aRng, bRng, and cRngto each other is not specified?
其他奇怪之处：uRng.Rows.Count = 1和uRng.Columns.Count = 10。我期望uRng3x10 范围是错误的吗？还是因为aRng, bRng, 和cRng彼此的位置未指定而未定义？
回答by mr.Reband
They are actually treated as three separate ranges, accessible from uRngvia the Range.Areasproperty:
它们实际上被视为三个独立的范围，可uRng通过Range.Areas属性访问：
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.areas(v=office.11).ASPX
Using your example, here's how to get A4included, as the first cell in the second range:
使用您的示例，以下是如何A4包含在第二个范围内的第一个单元格中：
Sub foo()
Dim aRng As Range: Set aRng = ActiveSheet.Range("A1:J1")
Dim bRng As Range: Set bRng = ActiveSheet.Range("A4:J4")
Dim cRng As Range: Set cRng = ActiveSheet.Range("A10:J10")
Dim uRng As Range: Set uRng = Union(aRng, bRng, cRng)
uRng.Style = "Good"
uRng.Areas(2).Cells(1, 1).Style = "Bad"
End Sub
also, the code below will give you same result:
此外，下面的代码会给你相同的结果：
Sub foo2()
Dim uRng As Range: Set uRng = [A1:J1,A4:J4,A10:J10]
uRng.Style = "Good"
uRng.Areas(2).Cells(1, 1).Style = "Bad"
End Sub
回答by Siddharth Rout
AuRng.Cells(2, 1)works only for contiguous ranges and not non-contiguous range. To select a cell in a particular area, you have to address that area and then use .Cells(r,w)
AuRng.Cells(2, 1)仅适用于连续范围而不适用于非连续范围。要选择特定区域中的单元格，您必须寻址该区域，然后使用.Cells(r,w)
For example
例如
uRng.Areas(n).Cells(r, c)
Where nis the area which you want to write to and r, care the row/column.
哪里n是要写入和地区r，c是行/列。
B.Rows.Count works with 1 Area. So to find the rows count in a non-contiguous range, you will have to loop though it.
B.Rows.Count 适用于 1 个区域。因此，要在非连续范围内查找行数，您必须循环遍历它。
See this example
看这个例子
Option Explicit
Sub foo()
Dim aRng As Range: Set aRng = ActiveSheet.Range("A1:J1")
Dim bRng As Range: Set bRng = ActiveSheet.Range("A4:J4")
Dim cRng As Range: Set cRng = ActiveSheet.Range("A10:J10")
Dim rngArea As Range
Dim rwCount As Long
Dim uRng As Range: Set uRng = Union(aRng, bRng, cRng)
If uRng.Areas.Count > 1 Then
Debug.Print "It's a non-contiguous range"
For Each rngArea In uRng.Areas
rwCount = rwCount + rngArea.Rows.Count
Next
Debug.Print "The range has " & rwCount & " rows"
Else
Debug.Print "It's a contiguous range"
Debug.Print "The range has " & uRng.Rows.Count & " rows"
End If
End Sub
C.There is an oddity in columns.countas well but since all the range had same number of columns, you didn't relaize this. Try this
C.也有一个奇怪的地方columns.count，但由于所有范围都有相同数量的列，你没有重新定义这一点。尝试这个
Option Explicit
Sub foo()
Dim aRng As Range: Set aRng = ActiveSheet.Range("A1:J1")
Dim bRng As Range: Set bRng = ActiveSheet.Range("A4:K4") '<~~ I changed this.
Dim cRng As Range: Set cRng = ActiveSheet.Range("A10:J10")
Dim uRng As Range: Set uRng = Union(aRng, bRng, cRng)
'~~> This will still give you 10 instead of `11`
Debug.Print uRng.Columns.Count
End Sub
So the same logic applies to find the number of columns as well.
所以同样的逻辑也适用于查找列数。
IMP: Now It depends on how you want to count the number of rows/columns. What if the rows/columns of the areas overlap? In such a case do you want to total count of the rows/columns or the actual Excel rows/columns in this non contiguous range?
IMP：现在这取决于您想如何计算行/列数。如果区域的行/列重叠怎么办？在这种情况下，您是否要计算此非连续范围内的行/列或实际 Excel 行/列的总数？
