All geek questions in one place

How to combine several Range objects into one, to be used as a chart source

I am trying to create a chart with multiple columns as the source area. Basically, I want to select specific columns where I skip some columns and combine them into one range. I set up a loop where I create a range, and add its address to the string, and separate them with a comma. I am sure that is exactly how Excel wants to format it.

BUT, I cannot create a new range from this line.

I hope someone here helps me.

I would really like to avoid, you need to copy the columns to a new sheet and just mark everything as a range.

I have the following code to create a 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

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

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

When printing 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

It seems that the same union will do.

+4
source share
2 answers

As discussed in the comments above, the best way to deal with this is to use native VBA functions like Union .

You can find some links on how to use this:

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

+7
source

Following the JMax guide, I ended up using Union. This is the code I ended up in. For the first time through the loop, I set CombinedRange to my actual range and subsequent runs, I union.

 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
+2
source

Source: https://habr.com/ru/post/1403826/

More articles:


All Articles