none
Is it possible to assign unique value to each cell of union Range? RRS feed

  • Question

  • e.g. I have a Range made from several other Range objects using Union operations; is it possible to assign distinct value for each cell of this Range?
    Monday, August 29, 2011 10:11 AM

All replies

  • You need to iterate the range collection and add vakue to each cell. The below VBA code shows one way:

    Sub UnionRange_Unique()
    
    Dim rn1 As Range
    Dim rn2 As Range
    Dim rn3 As Range
    
    Dim rnCell As Range
    
    Dim rnUnion As Range
    
    Dim i As Integer
    
    With ThisWorkbook.Worksheets(1)
      Set rn1 = .Range("A1")
      Set rn2 = .Range("A4")
      Set rn3 = .Range("B2")
    End With
    
    Set rnUnion = Application.Union(rn1, rn2, rn3)
    
    For Each rnCell In rnUnion
      rnCell = 10 + i
      i = i + 1
    Next
    
    



    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Monday, August 29, 2011 3:23 PM
  • using Consolidate.

    1.

     

    2. code

        private void Sheet1_Startup(object sender, System.EventArgs e)
        {     
          string[] source = { this.Range["a3:c5",missing].get_Address(missing,missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlR1C1,missing,missing)
                     ,this.Range["e5:g6",missing].get_Address(missing,missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlR1C1,missing,missing)};
          this.Range["a10", missing].Consolidate(source, Excel.XlConsolidationFunction.xlSum, false, true, false);
    

    3. result

     

    I hope this helps.


    http://vsto.tistory.com
    Tuesday, August 30, 2011 1:50 AM
  • You need to iterate the range collection and add vakue to each cell. 

    This will be extremely slow, as it will trigger recalculation on each assignment.
    Tuesday, August 30, 2011 3:49 AM
  • user4756.

    Turn the recalculation temporarily off when populating the range. However, the consolidation approach by VSTO_Beginner will speed it up considerably (very nice!). Still I suggest to turn the recalculation off. 


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Tuesday, August 30, 2011 1:42 PM
  • Turning off/on calculation makes code perform even slower, because it leads to a full and complete recalculation of everything. 

    And consolidation is not what I need. My task was different, see this code:

     

    var range = sheet.Range["A1", "F1"];

    var range2 = sheet.Range["A3", "D3"];

    var union = range.Application.Union(range, range2);

    union.Value = someMatrix;

    Thursday, September 1, 2011 1:46 PM
  •  var range = sheet.Range["A1", "F1"];

     var range2 = sheet.Range["A3", "D3"];

     var union = range.Application.Union(range, range2);

    union.Value = someMatrix;

     

     

    I don't know what you want exactly. Plsease, check the below source.

    I used temp Range : aa1:aj1

    Considering that the source you wrote, I think what I suggest above is one of the fastest way.

     

                int[] s =  { 1, 2, 3, 4, 5,6,7,8,9,10};
    
                this.Range["aa1:aj1", missing].Value2 = s;
                this.Range["a1:f1", missing].Value2 = this.Range["aa1:af1",missing].Value2;
                this.Range["a3:d3", missing].Value2 = this.Range["ag1:aj1", missing].Value2;
    
                this.Range["aa1:aj1", missing].Clear();
    


    http://vsto.tistory.com
    Monday, September 5, 2011 1:11 AM
  • I don't know what you want exactly. Plsease, check the below source.

    I need to assign values (different values!) to multiple ranges at once, without triggering recalculation on each assignment. And without turning calculation off/on, because this leads to full (not partial) recalculation of everything.
    Tuesday, September 13, 2011 12:00 PM
  • When is it appropiated to recalculate formulas etc that have been changed?

    You may consider to set the default value for the Workbook Calculation to "Manual". In that way You have full control when to recalculate. 


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Tuesday, September 13, 2011 1:16 PM
  • When is it appropiated to recalculate formulas etc that have been changed?

    When all changes in my batch are done (not after each change!)
    Wednesday, September 14, 2011 5:13 AM
  • Did You only read the first line of my previously message?


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Wednesday, September 14, 2011 12:58 PM
  • Did You only read the first line of my previously message?


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    The idea in second line is not appropriate for my task.
    Thursday, September 15, 2011 4:26 AM
  • See Charles Williams excellent write up about calculation, pay attention to the WorksheetEnableCalculation approach.

    http://www.decisionmodels.com/calcsecretsh.htm

    (There exist some issues with his site as he uses embedded pages in a frame.)

    Hopefully it will give You a solution.


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Thursday, September 15, 2011 3:14 PM