Order of Union Range merging

[RawlinsCross]

I have a listbox on a userform that has a couple dozen entries, each entry corresponds to a column of data in a larger worksheet. Roughly the steps are:

#1 Find mrXValues from a set of two DTPickers
#2 Union mrXValues to mrTotal which will be used to graph
#3 Find the first series
#4 Union that series to MrTotal
#5 Repeat until all series from the listbox are sourced
#6 Plot the data

The listbox order Date,1,2,3,4,5,6
shows up in MrTotal as Date2,4,6,1,3,5

which makes it impossible to apply a legend using the Listbox.

Any thought as to what is going on?

Code:

Public Sub Plot1_Click()
Dim i As Long
Dim lIDCount As Long
Dim item As Variant
Dim lColNdx As Long
Dim sDataIdentifier As String

Set mrYValues = Nothing
Set mrTotal = Nothing

msICP = "Aqu_"

'finding the XData
 Set mrXValues = rGetXValuesRange(wks:=mwks, _
   dtStart:=Me.DTPicker1.Value, dtEnd:=Me.DTPicker2.Value)

 Set mrTotal = mrXValues

'Series Data
For i = 0 To Me.Select_Analysis.ListCount - 1
    sDataIdentifier = msICP & Me.Select_Analysis.List(i)
    lColNdx = lGetHeaderColNumber(wks:=mwks, _
   sDataIdentifier:=sDataIdentifier)
   sDataIdentifier = msICP & mwks.Cells(1, lColNdx) & msSelectedElement
    lColNdx = lGetHeaderColNumber(wks:=mwks, _
   sDataIdentifier:=sDataIdentifier)
    Set mrYValues = mrXValues.Offset(0, lColNdx - 1)
    Set mrTotal = Union(mrTotal, mrYValues)
Next I

'Make Chart
If Not mrTotal Is Nothing Then
    Call MakeChart
End If


'After this is a MakeChart() which plots the graph using
'Set myChart = ActiveSheet.Shapes.AddChart(xlXYScatter).Chart
'With myChart
'   .SetSourceData Source:=mrTotal, PlotBy:=xlColumns
End Sub

 

[RawlinsCross]

Okay, think I may know what's going on here - wonder if I could get feedback. I'm joining individual columns (ranges) of data into a larger range using Union

From what I can determine, when Application.Union executes, it reorganizes the data so that it is in order from column A to ZZZ (or whatever). All my final ranges start with the date which is convenient for the Application as it is in column A. If my next column to be added is in column Z, then no problem. However, if the next column is in column Y then Application union reorganizes the final range as (A:Y:Z) instead of (A:Z:Y).

Any thoughts on a get-around?

 

[BiocideJ]

You are correct. Application.Union will re-order the ranges from A->ZZZ and 1>999. It basically follows the same logic that if you hold CONTROL and make multiple selections, when you copy and paste it, it doesn't matter what order you selected the ranges in, it will paste them left to right and top-down.

The only work-around (that I can think of) to this would be to copy the ranges to a temporary sheet in the order you want and then use THAT data as the chart source. This way you can control the column orders.

 

[Fluff]

If the rows are contiguous, you could use an array to collect the data & then copy that to another sheet for the chart.

 

[RawlinsCross]

Yes, where the listbox entries (used to form the legend) didn't match up with how Union reorganized the column ranges, it was a bleedin' nightmare. Thanks for the heads up on that quirk.

Ended up abandoning Application.Union to store the ranges in a collection which I declared as a member collection in the userform (so that I could use it in MakeChart function):

Public Sub Plot1_Click()
Dim i As Long
Dim lColNdx As Long
Dim sDataIdentifier As String

msICP = "Aqu_"

Set mrangeCollection = New Collection

'finding the XData using a function based on two DTPickers
 Set mrXValues = rGetXValuesRange(wks:=mwks, _
   dtStart:=Me.DTPicker1.Value, dtEnd:=Me.DTPicker2.Value)

'Now using a member Collection
For i = 0 To Me.Select_Analysis.ListCount - 1
    sDataIdentifier = msICP & Me.Select_Analysis.List(i)
    lColNdx = lGetHeaderColNumber(wks:=mwks, sDataIdentifier:=sDataIdentifier)
    sDataIdentifier = msICP & mwks.Cells(1, lColNdx) & msSelectedElement
    lColNdx = lGetHeaderColNumber(wks:=mwks, sDataIdentifier:=sDataIdentifier)
    Set mrYValues = mrXValues.Offset(0, lColNdx - 1)
    mrangeCollection.Add mrYValues
Next I

'Y-axis axis title for chart
msAxisTitle = mwks.Cells(4, lColNdx)

If Not mrangeCollection Is Nothing Then
    Call MakeChart
End If
End Sub

MakeChart function where the Collection is used:

Private Sub MakeChart()


 '--creates a new chart, makes a jpg image and displays the image
 Dim myChart As Chart
 Dim dblZoomSave As Double
 Dim ImageName As String
 Dim i As Integer



 Application.ScreenUpdating = False
 '--store current zoom
 dblZoomSave = ActiveWindow.Zoom
 ActiveWindow.Zoom = 120

 Set myChart = ActiveSheet.Shapes.AddChart(xlXYScatter).Chart
 
'Uses .Values stored in the Collection
 With myChart
   For i = 0 To Me.Select_Analysis.ListCount - 1
    With .SeriesCollection.NewSeries
        .XValues = mrXValues
        .Values = mrangeCollection(i + 1)
        .Name = Me.Select_Analysis.List(i)
        .MarkerSize = 2
    End With
   Next i
   .HasLegend = True
   .HasTitle = True
   .HasTitle = False
   .Legend.Position = xlLegendPositionBottom
   
   With .Legend
    .Font.Size = 8
   End With



'Some standard formatting with axis labels
   With .Axes(xlCategory)
        If Me.DTPicker2.Value - Me.DTPicker1.Value < 365 Then
            .TickLabels.NumberFormat = "[$-409]mmm-dd;@"
        Else
            .TickLabels.NumberFormat = "[$-409]mmm-yy;@"
        End If
   End With


   With .Axes(xlValue)
      .TickLabels.NumberFormat = "#,##0"
      .HasTitle = True
      .AxisTitle.Text = msAxisTitle
   End With
 End With


'Export File and Store in Userform Picture control
 ImageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.jpeg"
 myChart.Export filename:=ImageName
 Application.DisplayAlerts = False
 ActiveSheet.ChartObjects(1).Delete
 Application.DisplayAlerts = True


 '--reset zoom
 ActiveWindow.Zoom = dblZoomSave
 Application.ScreenUpdating = True
 Me.Image1.Picture = LoadPicture(ImageName)

End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback