vba 如何将多个 Range 对象合并为一个，用作 Chart 源

How to merge multiple Range objects, into one, for use as Chart source

提问by Nicolai

I'm trying to make a chart, with multiple columns as source area. Basically, I want to select specific columns, where I skip some columns, and merge them all into one range. I've setup a loop, where I create a range, and append it's address to a string, and seperates them with a comma. I'm pretty sure this is how Excel wants it formatted.

我正在尝试制作一个图表，多列作为源区域。基本上，我想选择特定的列，在其中跳过一些列，并将它们全部合并到一个范围内。我已经设置了一个循环，我在其中创建了一个范围，并将它的地址附加到一个字符串中，并用逗号分隔它们。我很确定这就是 Excel 希望格式化的方式。

BUT, I cannot seem to create a new range from this string.

但是，我似乎无法从此字符串创建新范围。

I hope someone here can help me out.

我希望这里有人可以帮助我。

I would very much like to avoid, having to copy the columns to a new sheet, and just mark it all as a range.

我非常想避免，必须将列复制到新工作表，并将其全部标记为一个范围。

I have the following code, for making the combined range:

我有以下代码，用于制作组合范围：

'Loops for each number of sections
For Z = 1 To Sheet1.txtNoSections

    'Get gauge to use
    Section = Workbooks(ThisWorkbook.Name).Worksheets(1).Cells(26 + Z, 6).Value
    'Sets varibel for distance from root
    Dist = Workbooks(ThisWorkbook.Name).Worksheets(1).Cells(26 + Z, 3).Value
    'Get range to use
    Set ChartRange = ActiveSheet.Range(ActiveCell, ActiveCell.Offset(rc, Section))
    RangeString = RangeString & ChartRange.AddressLocal
    If Z <> 1 Then
        RangeString = RangeString & ","
    End If

Next Z

I have then tried to get a new range with something like this, but no luck.

然后我试图用这样的东西来获得一个新的范围，但没有运气。

Dim ActualRange As Range
Set ActualRange = ActiveSheet.Range(RangeString)

When printing the RangeString, it looks like this: $S$2$V$6181$S$2:$X$6181,$S$2:$Z$6181,$S$2:$AB$6181,$S$2:$AD$6181,$S$2:$AF$6181,$S$2:$AH$6181,$S$2:$AJ$6181,$S$2:$AL$6181,$S$2:$AN$6181,$S$2:$AP$6181,$S$2:$AR$6181,$S$2:$AT$6181,$S$2:$AV$6181,$S$2:$AX$6181,$S$2:$AZ$6181,$S$2:$BB$6181,$S$2:$BD$6181,$S$2:$BF$6181,$S$2:$BH$6181,$S$2:$BJ$6181,$S$2:$BL$6181,$S$2:$BN$6181,$S$2:$BP$6181

打印 RangeString 时，它看起来像这样： $S$2$V$6181$S$2:$X$6181,$S$2:$Z$6181,$S$2:$AB$6181,$S$2:$AD$6181,$S$2:$AF$6181,$S$2:$AH$6181,$S$2:$AJ$6181,$S$2:$AL$6181,$S$2:$AN$6181,$S$2:$AP$6181,$S$2:$AR$6181,$S$2:$AT$6181,$S$2:$AV$6181,$S$2:$AX$6181,$S$2:$AZ$6181,$S$2:$BB$6181,$S$2:$BD$6181,$S$2:$BF$6181,$S$2:$BH$6181,$S$2:$BJ$6181,$S$2:$BL$6181,$S$2:$BN$6181,$S$2:$BP$6181

Seems like the same union would do.

似乎同一个工会会做。

回答by JMax

As discussed in the comments above, the best way to handle this is to use native VBA functions such as Union.

正如上面的评论中所讨论的，处理这个问题的最好方法是使用本机 VBA 函数，例如Union.

You can find several references on how to use this:

您可以找到有关如何使用它的多个参考资料：

• on Daily dose of Excel
• on vba Express
• even a "better" Union on Chip Pearson's website

• 在每日剂量的Excel
• 在vba Express 上
• 甚至是Chip Pearson 网站上“更好”的工会

Yet, please note that you can answer you own question (it is even highly recommended) and accept it. This way, you can share your knowledge with the community and the way you've solved your issue with your own code.
IMHO, this would be even better than accepting my answer.

但是，请注意，您可以回答自己的问题（甚至强烈推荐）并接受它。这样，您可以与社区分享您的知识以及您使用自己的代码解决问题的方式。
恕我直言，这比接受我的回答更好。

回答by Nicolai

Following JMax's guidance, I ended up using Union. This is the code I ended up with. The first time through the loop, I set the CombinedRange to my actual range, and the subsequent runs, I union.

按照 JMax 的指导，我最终使用了 Union。这是我最终得到的代码。第一次通过循环时，我将 CombinedRange 设置为我的实际范围，随后的运行我并集。

For Z = 1 To Sheet1.txtNoSections

    'Get gauge to use
    Section = Workbooks(ThisWorkbook.Name).Worksheets(1).Cells(26 + Z, 6).Value
    'Get range to use
    Set ChartRange = ActiveSheet.Range(ActiveCell, ActiveCell.Offset(rc, 0))
    Debug.Print "ChartRange(" & Z & "): " & ChartRange.Address
    If Z = 1 Then
        Set CombinedRange = ChartRange
    Else
        Set CombinedRange = Union(CombinedRange, ChartRange)
    End If
    ActiveCell.Offset(0, 5).Activate

Next Z
Debug.Print "Combined: " & CombinedRange.Address