none
excel 2010 -- UNION two named ranges and use the resulting range as the Horizontal (Category) Axis Labels of a chart RRS feed

  • Question

  • This post is related to another post of mine from a few days ago: How do I reference a column from a named range as a second series in a line chart in Excel 2010?    I'm still trying to come up with a solution to that post and thought I'd give the UNION a try.

    I have a workbook with two one-column named ranges containing dates, "backlogDates" and "sentDates".  The ranges are not the same height.  Some of the date values in the two named ranges will overlap.

    I want to combine the two named ranges into a third range, "myCombinedRange", and use myCombinedRange as the Horizontal (Category) Axis Labels source in a chart on a worksheet named "Sheet2".

    I looked at Application.Union Method (Excel) and a few pages I found using Google and a few posts in this forum that contain .Union, but haven't got working code yet.  Here's an example that I just tested:

        Worksheets("Sheet2").Activate 'this is probably redundant due to the With block below
        Dim myApp As Application
        Dim myCombinedRange As Range
    
        With Worksheets("Sheet2")
            Set myCombinedRange = myApp.Union(Range("backlogDates"), Range("sentDates"))
        End With

    This attempt is throwing "Run-time error '91': Object variable or With block variable not set"

    I've tried a number of variations on the above code, including declaring Range objects for both named ranges and assigning the existing named ranges to the newly-declared range objects and using them in place of the quoted named ranges (that throws different errors), including the workbook name with the named range, e.g., "faxblasts.xlsx!backlogDates", excluding the "Set" keyword, and other variations, all to no avail.

    Assuming what I'm trying to do is possible and one of you is able to come up with functioning code, how then would I set the Horizontal (Category) Axis Labels source of the chart to "myCombinedRange"?  I don't see a "source" property for the Chart.Axes object.

    In the alternative, how could I write the values of the combined ranges to column A of a worksheet and use it as the Horizontal (Category) Axis Labels source of the chart?

    Thanks for any suggestions

    Christian Bahnsen

    Thursday, August 18, 2016 6:22 PM

Answers

  • David,

    Thanks for the reply.  Well, I've come up with a workaround.  Instead of using the .Union method I used a collection to join the values from the two named ranges, wrote the collection to an array, then wrote the array to a worksheet using WorksheetFunction.Transpose.  I also used the .RemoveDuplicates method you suggested to eliminate duplicate dates.

    Here's the code I used:

    Sub useCollection()
    
        Dim myCollection As New Collection
        Dim myRange As Range
        Dim myArray() As Variant
        Dim myRangeDef As String
        
        ' let's get the first range
        Set myRange = Application.Range("range1")
        
        ' and add the values from the range to myCollection
        For Each a In myRange.Cells
    
            myCollection.Add a.Value
    
        Next
        
        ' now let's get the second range
        Set myRange = Application.Range("range2")
        
        ' and add the values from the range to myCollection
        For Each a In myRange.Cells
    
            myCollection.Add a.Value
    
        Next
        
        ' redimension the myArray
        ReDim myArray(1 To myCollection.Count) As Variant
        
        ' and write the collection to the array
        For i = 1 To myCollection.Count
        
            myArray(i) = myCollection(i)
        
        Next
        
        ' build a reference to a range
        myRangeDef = "E1:E" + Trim(Str(myCollection.Count))
        
        ' paste the array onto the active sheet
        ActiveSheet.Range(myRangeDef).Value = WorksheetFunction.Transpose(myArray)
        
        ' remove duplicate dates
        ActiveSheet.Range(myRangeDef).RemoveDuplicates Columns:=Array(1)
        
        Set myCollection = Nothing
        
    
    End Sub

    This screenshot shows the ranges and the resultset:

    This has been an interesting exercise.  I'm not going to continue to pursue the goal of using the resultset as the source for the Horizontal (Category) Axis Labels because it's going to be much easier for me to prep all the data for a chart in SQL Server and just paste it into Excel.  As Jon Peltier says: "You can spend five minutes with your data, and save yourself five hours of frustration and aggravation."

    Thanks again for your help.

    Chris

    • Proposed as answer by David_JunFeng Tuesday, August 30, 2016 1:45 AM
    • Marked as answer by David_JunFeng Tuesday, August 30, 2016 8:55 AM
    Thursday, August 25, 2016 5:42 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards

    Emi Zhang

    TechNet Community Support

    Please mark the reply as an answer if they help and unmark them if they provide no help.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.


    Friday, August 19, 2016 9:23 AM
  • >>>This attempt is throwing "Run-time error '91': Object variable or With block variable not set"

    According to your description, you could modify your code like below:
    Worksheets("Sheet2").Activate 'this is probably redundant due to the With block below
    
    Dim myCombinedRange As Range
    
    With Worksheets("Sheet2")
         Set myCombinedRange = Application.Union(Range("backlogDates"), Range("sentDates"))
    End With

    Monday, August 22, 2016 2:52 AM
  • David,

    Thanks for your suggestion.  The error changes to: "Run-time error '1004': Method 'Union' of object '_Application' failed"

        Worksheets("Sheet2").Activate 'this is probably redundant due to the With block below
    
        Dim myCombinedRange As Range
    
        With Worksheets("Sheet2")
            Set myCombinedRange = Application.Union(Range("backlogDates"), Range("sentDates"))
        End With

    Monday, August 22, 2016 1:36 PM
  • >>>Thanks for your suggestion.  The error changes to: "Run-time error '1004': Method 'Union' of object '_Application' failed"

    According to your description, I am not able to reproduce this issue.

    Sub Demo()
    
        Worksheets("Sheet2").Activate 'this is probably redundant due to the With block below
    
        Dim myCombinedRange As Range
    
        With Worksheets("Sheet2")
            Set myCombinedRange = Application.Union(Range("backlogDates"), Range("sentDates"))
        End With
        
        
        myCombinedRange.Copy
        ActiveSheet.Paste
        
    End Sub

    The result:



    So I suggest that you could unload your Excel file on OneDrive, that will help us reproduce and resolve your issue.

    Thanks for your understanding.

    Tuesday, August 23, 2016 2:20 AM
  • David,

    Thanks for the reply.  The screenshot below illustrates what I was trying to achieve, but also shows I had an incorrect expectation of my resulting combined range.  Your example worked because both ranges had the same height, but the resulting range was two columns wide.  I was trying to come up with a one-column range where, in effect, range2 was appended to the bottom of range1.  I don't think that's possible.  When the code tries to execute "myCombinedRange.Copy" I'm getting "Run-time error '1004': This action won't work on multiple selections."

    If it had worked I was interested to see how it would handle duplicate dates, but it's a moot point because the resulting range would be "side-by-side" instead of "stacked".

    Thanks to all for the feedback/suggestions.

    Unless someone knows of a way to "stack" the ranges, I guess the answer to my post is: "it can't be done"

    Christian

    Wednesday, August 24, 2016 4:07 PM
  • Hi Christian.Bahnsen,

    According to your screenshot and description, you could use Range.Copy method to copy the range to the specified range, please refer to Range.Copy Method (Excel)

    And use Range.RemoveDuplicates method to remove duplicate values from a range of values, please refer to Range.RemoveDuplicates Method (Excel)

    Thanks for your understanding.

    Thursday, August 25, 2016 8:04 AM
  • David,

    Thanks for the reply.  Well, I've come up with a workaround.  Instead of using the .Union method I used a collection to join the values from the two named ranges, wrote the collection to an array, then wrote the array to a worksheet using WorksheetFunction.Transpose.  I also used the .RemoveDuplicates method you suggested to eliminate duplicate dates.

    Here's the code I used:

    Sub useCollection()
    
        Dim myCollection As New Collection
        Dim myRange As Range
        Dim myArray() As Variant
        Dim myRangeDef As String
        
        ' let's get the first range
        Set myRange = Application.Range("range1")
        
        ' and add the values from the range to myCollection
        For Each a In myRange.Cells
    
            myCollection.Add a.Value
    
        Next
        
        ' now let's get the second range
        Set myRange = Application.Range("range2")
        
        ' and add the values from the range to myCollection
        For Each a In myRange.Cells
    
            myCollection.Add a.Value
    
        Next
        
        ' redimension the myArray
        ReDim myArray(1 To myCollection.Count) As Variant
        
        ' and write the collection to the array
        For i = 1 To myCollection.Count
        
            myArray(i) = myCollection(i)
        
        Next
        
        ' build a reference to a range
        myRangeDef = "E1:E" + Trim(Str(myCollection.Count))
        
        ' paste the array onto the active sheet
        ActiveSheet.Range(myRangeDef).Value = WorksheetFunction.Transpose(myArray)
        
        ' remove duplicate dates
        ActiveSheet.Range(myRangeDef).RemoveDuplicates Columns:=Array(1)
        
        Set myCollection = Nothing
        
    
    End Sub

    This screenshot shows the ranges and the resultset:

    This has been an interesting exercise.  I'm not going to continue to pursue the goal of using the resultset as the source for the Horizontal (Category) Axis Labels because it's going to be much easier for me to prep all the data for a chart in SQL Server and just paste it into Excel.  As Jon Peltier says: "You can spend five minutes with your data, and save yourself five hours of frustration and aggravation."

    Thanks again for your help.

    Chris

    • Proposed as answer by David_JunFeng Tuesday, August 30, 2016 1:45 AM
    • Marked as answer by David_JunFeng Tuesday, August 30, 2016 8:55 AM
    Thursday, August 25, 2016 5:42 PM
  • Hi ,

    You are welcome, if you have any issue, please feel free post on MSDN forum

    Thanks for your understanding.
    Friday, August 26, 2016 9:57 AM