+ Reply to Thread
Results 1 to 6 of 6

Does filling part of an array from a range re-dimension the array?

  1. #1
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Does filling part of an array from a range re-dimension the array?

    Hi Everyone
    I want to create an array with "spare" space to fill later based on some tests.
    The example test code snippet below seems to suggest that filling part of the 'large' array from a range redimensions it down to the size of the range?
    Is this the case and do I therefore need to ReDim again .. and again ...?

    Please Login or Register  to view this content.
    Last edited by barryleajo; 08-04-2011 at 10:05 AM.

  2. #2
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,207

    Re: Does filling part of an array from a range re-dimension the array?

    This line
    Please Login or Register  to view this content.
    assigns the variable myarray to the array of values in A1:B9. Nowhere does it specify "part of the array" or "starting at 1,1". After executing that line, myarray is a 9 X 2 array.

    To partialy fill an array, you could use something like this

    Please Login or Register  to view this content.
    Alternatly, you could fill the array with the small data set and then expand the last index to your needs.
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Forum Contributor
    Join Date
    04-21-2007
    Location
    Lima, Peru
    MS-Off Ver
    2000, 2007, 2010
    Posts
    674

    Re: Does filling part of an array from a range re-dimension the array?

    Hi

    You can see the effect in the locals window if you step through the macro. As you are making the array equal to the range you are to all intents and purposes redimensioning the array.

    Why not simply loop through the data to populate the parts of the array you need, as you are writing to an array it is very fast and takes a fraction of a second.

    Regards

    Jeff

  4. #4
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Does filling part of an array from a range re-dimension the array?

    hi, barryleajo, you can either take "A1:E9" into the array and fill 3,4,5 columns with new data or create a separate array with the same number of rows and 3 columns.

  5. #5
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Does filling part of an array from a range re-dimension the array?

    Mike
    Great explanation and approaches to solve my problem.
    Very many thanks
    Barry

  6. #6
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Does filling part of an array from a range re-dimension the array?

    @ Jeff
    I'll try this - thank you
    @watersev
    thanks for a couple more options

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1