excel vba 如何将多个非连续范围的值复制到数组中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13246885/
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 how to copy the value of multiple non-contiguous ranges into an array
提问by Siraj Samsudeen
I am trying to copy the value of multiple non-contiguous ranges into an array. I wrote code like this:
我正在尝试将多个非连续范围的值复制到一个数组中。我写了这样的代码：
summaryTempArray = .range("A2:D9,A11:D12,A14:D15").Value
But it copies only the first part (A2:D9). Then, I tried the following and I get the error - "Method Union of Object _Global Failed" - is there any mistake in the way that I am using union?
但它只复制第一部分 (A2:D9)。然后，我尝试了以下操作，但出现错误 - “对象 _Global 的方法联合失败” - 我使用联合的方式是否有任何错误？
summaryTempArray = Union(.range("A2:D9"), .range("A11:D12"), .range("A14:D15")).Value
回答by Jook
Don't know what was wrong with your union, but it would have created the same range, which you stated in your first attempt.
不知道您的 有什么问题union，但它会创建与您在第一次尝试中陈述的范围相同的范围。
The problem is, you have now multiple areas. Which you can, and as far as I know, has to address now.
问题是，您现在有多个区域。你可以，据我所知，现在必须解决。
Here is an example, which will resolve in an array of all areas, without adding each cell individually, but adding each area individually to the summary array:
这是一个示例，它将解析所有区域的数组，而不是单独添加每个单元格，而是将每个区域单独添加到汇总数组中：
Public Sub demo()
Dim summaryTempArray() As Variant
Dim i As Long
With Tabelle1
ReDim summaryTempArray(1 To .Range("A2:D9,A11:D12,A14:D15").Areas.Count)
For i = 1 To .Range("A2:D9,A11:D12,A14:D15").Areas.Count
summaryTempArray(i) = .Range("A2:D9,A11:D12,A14:D15").Areas(i)
Next i
End With
End Sub
Hope this helps.
希望这可以帮助。
回答by Tony Dallimore
I believe Jook's solution is as good as you are going to get if it is important to get the source ranges into an array. However, I think the solution should include instructions on extracting values from a ragged array. This is not difficult but the syntax is obscure.
如果将源范围放入数组很重要，我相信 Jook 的解决方案与您将获得的一样好。但是，我认为解决方案应该包括从参差不齐的数组中提取值的说明。这并不难，但语法晦涩难懂。
I cannot get your Unionstatement to fail either. I assume there is something about the context that causes the failure which I cannot duplicate.
我也不能让你的Union陈述失败。我假设有一些关于导致我无法复制的失败的上下文。
The code below shows that the two ranges are the same and that only the first sub-range is loaded to an array as you reported. It finishes with an alternative approach that might be satisfactory.
下面的代码显示两个范围是相同的，并且只有第一个子范围被加载到您报告的数组中。它以一种可能令人满意的替代方法结束。
Option Explicit
Sub Test()
Dim CellValue() As Variant
Dim rng As Range
With Worksheets("Sheet1")
Set rng = .Range("A2:D9,A11:D12,A14:D15")
Debug.Print rng.Address
Set rng = Union(.Range("A2:D9"), .Range("A11:D12"), .Range("A14:D15"))
Debug.Print rng.Address
' The above debug statements show the two ranges are the same.
Debug.Print "Row count " & rng.Rows.Count
Debug.Print "Col count " & rng.Columns.Count
' These debug statements show that only the first sub-range is included the
' range counts.
CellValue = rng.Value
Debug.Print "Rows " & LBound(CellValue, 1) & " to " & UBound(CellValue, 1)
Debug.Print "Cols " & LBound(CellValue, 2) & " to " & UBound(CellValue, 2)
' As you reported only the first range is copied to the array.
rng.Copy Destination:=Worksheets("Sheet2").Range("A1")
' This shows you can copy the selected sub-ranges. If you can copy the
' required data straight to the desired destination, this might be a
' solution.
End With
End Sub
回答by KiwiG
I had the same problem & tried a few methods without success until I hit on this:-
我遇到了同样的问题并尝试了一些方法但没有成功，直到我遇到了这个问题：-
dim i as integer
Dim rng1 as range
Dim str as string
dim cels() as string
Set rng1 = sheet1.Range("A2:D9,A11:D12,A14:D15")
str = rng1.address(0,0)
cels() = split(str, ",") '<--- seems to work OK
for i = 0 to 2
Debug.Print cels(i)
Next i
I would be interested if this is an "incorrect" conversion method.
如果这是一种“不正确”的转换方法，我会很感兴趣。
