locked
How to put discontiguous range into array in a statement? RRS feed

  • Question

  • Hi

    myArray = [A1:C4]

    will put all cells into myArray. Great.

    But, if range is discontiguous:

    myArray = [$A$2:$C$2,$A$4:$C$4]

    will only put the last row into myArray. 

    How to put all rows of a discontiguous range into myArray without looping?

    thx!

    Friday, July 8, 2016 8:04 PM

All replies

  • Hi

    I don't know what you're doing but this works for me.

    Sub aray()
    For Each c In Array("$A$2:$C$2", "$A$4:$C$4")
    Range(c) = "Qty"
    Next c
    End Sub

    Try the macro on a blank Worksheet


    Cimjet


    Saturday, July 9, 2016 1:07 AM
  • Thx, Cimjet, but i specified "without looping". 

    Also, i'm trying to put the contents of all the cells into an array ("range into array")

    You're putting a single value into all the cells. 

    j


    • Edited by johny w Saturday, July 9, 2016 1:34 AM
    Saturday, July 9, 2016 1:30 AM
  • Hi

    I guess I didn't read the last line, sorry.

    You'll need to wait for someone with more experience then me.


    Cimjet

    Saturday, July 9, 2016 1:39 AM
  • @johny w,

    I'm afraid it is not possible.

    Why won't you loop? It is blazing fast.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Saturday, July 9, 2016 3:41 AM
  • with hundreds-of-thousands or millions of values, it might not be blazing fast. 

    Sunday, July 10, 2016 2:25 PM
  • Maybe it would be better to redesign your application structure. Do not put "hundreds-of-thousands or millions of values" in the Excel frontend. If possible, do as many data calculation in the backend, considering the data is incoming from a database.

    ---

    For what it is worth, I'd do something like this to diminish the number of loops:

    Sub Main()
        Dim AreasCollection As Collection
        Dim TestRange As Range
        Dim iArea As Range
        
        Set TestRange = Range("A2:C2,A4:C4,A6:C6")
        Set AreasCollection = New Collection
        
        For Each iArea In TestRange.Areas
            AreasCollection.Add TestRange.Value2
        Next iArea
    End Sub


    http://www.ambienteoffice.com.br - http://www.clarian.com.br


    Sunday, July 10, 2016 3:06 PM
  • How to put all rows of a discontiguous range into myArray without looping?

    For clarification and complement of the previous reviewers: That is not possible.

    Andreas.

    Sunday, July 10, 2016 3:36 PM
  •     Dim AreasCollection As Collection
    

    @Felipe, good thinking on the areas! But that's a collection. I'm looking for an array. 

    "That is not possible."

    For clarification and complement of the previous reviewers:
    "i don't know how" <> "That is not possible." 
    @Andreas, consider that possibility that you might not know everything. 

    thx

    Sunday, July 10, 2016 4:33 PM
  • Hi johny,

    >> myArray = [A1:C4] will put all cells into myArray.

    >> myArray = [$A$2:$C$2,$A$4:$C$4] will only put the last row into myArray.

    I made a test with myArray = [$A$2:$C$2,$A$4:$C$4], it will only output the first row. I would suggest you try suggestion from Cimjet to split “$A$2:$C$2,$A$4:$C$4” into two Range and use [$A$2:$C$2] and [$A$4:$C$4].

    It seems to be the limitation of Application.Evaluate which could not calculate “$A$2:$C$2,$A$4:$C$4”. With Excel Object Model, there is no way to achieve your requirement without looping. If this is a new feature you want to include in the future version of Excel, I suggest you submit a feedback in the link below:

    Reference: https://excel.uservoice.com/

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, July 11, 2016 10:04 AM
  • @Andreas, consider that possibility that you might not know everything. 

    If I would know everything, I would publish an article in physical review letters about dark matter and get a Nobel price for it. :-)

    Fortunately it is clear how to get data from cells into an array. You demand that it is possible to get an array from a Range object that contains areas.

    Even with looping, are you aware what this means? How can such a code look like?

    You want
      myArray = Range("A2:C2,A4:C4")

    But if that is possible the next one wants
      myArray = Range("A2:C2,F2:H2")

    And why not this
      myArray = Range("A2, D10, B3, F4:H2")

    Try to write a code that can solve this scenarios by yourself, then you will notice what you are asking for.

    Andreas.



    Monday, July 11, 2016 2:25 PM
  • with hundreds-of-thousands or millions of values, it might not be blazing fast. 

    In your example, you manually enter the array elements. If you're talking about millions of values, how exactly do you think you're going to build the array?

    Perhaps it would be helpful if you provided some insight into what this application is doing; why are you processing a million element array of non-contiguous elements and what are you doing with them? Why are the elements not stacked neatly into a data array?

    Without more detail, it does seem that your basic design doesn't match its intended use and you might be better served if you redesigned the system to handle millions of data elements.

    As an aside, you're always better off in data centric apps to have clean, well defined, data sets and then do reporting, computations, charting, whatever off the clean data. Too often people design sheets that are human friendly with lots of clever line breaks, subtotals, fancy fonts, etc and then find that what they really need is a big old pile-of-data to work with and some other method to generate human friendly outputs and computations.

    Monday, July 11, 2016 4:20 PM