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

Question

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.

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

Seems like the same union would do.

Answer 1

As discussed in the comments above, the best way to handle this is to use native VBA functions such as 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

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.