+ Reply to Thread
Results 1 to 4 of 4

Array is out of range with redim

  1. #1
    Forum Contributor colddeck84's Avatar
    Join Date
    06-18-2016
    Location
    bergen, norway
    MS-Off Ver
    2016
    Posts
    254

    Array is out of range with redim

    HI I was hoping someone could help me a little, I think its just a small error I have but I can not figure out how to fix it.

    Here is my code

    Please Login or Register  to view this content.
    But I keep getting out of range on the redim statement , after reading trough some forums its come to my understanding that can only redim the last dimension of a multi dimension array, but in my eyes it looks like its the last row im trying to add ???

    Any help making this work would be greatly appreciated ..


    regards frederik

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Array is out of range with redim

    can only redim Preserve the last dimension of a multi dimension array.

    You can ONLY Preserve the Last dimension.


    Please Login or Register  to view this content.
    Dim Lrow, i, j, k, c As Integer
    should be
    Dim Lrow As Long
    Dim i As Long, j As Long, k As Long, c As Long
    Last edited by AB33; 09-20-2017 at 05:33 AM.

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Array is out of range with redim

    Hi,

    Your code changes the value of j in the loop, and therefore you are trying to alter the first dimension too, which you cannot do if you use Preserve. You might calculate the count at the start to avoid resizing the array like this
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Forum Contributor colddeck84's Avatar
    Join Date
    06-18-2016
    Location
    bergen, norway
    MS-Off Ver
    2016
    Posts
    254

    Re: Array is out of range with redim

    Hi thanks to the both of you for answers.

    Dim Lrow, i, j, k, c As Integer
    should be
    Dim Lrow As Long
    Dim i As Long, j As Long, k As Long, c As Long
    Ok thanks for letting me know , I had is as long but when code was not working I made all kinds of changes.

    You might calculate the count at the start to avoid resizing the array like this
    About counting the size of the array first this is not ideal solution I think , in the sample i'm trying to remove past dates , but I have all kinds of custom filters add like to add using this code (if I can get it working) and my overall plan is to stop looping trough the spreadsheet anymore.
    is there another way to make this array dynamically ?

    Edit:

    tested code as suggested by AB33 , this was lightning fast , however it did offset the entire range 1 both directions , but it looks like all data is stored in vResults as I would expect ,
    testing by just adding like this: Range("C12").Value = vResult(1, 3) gives correct results , but when I try to add everything the range gets offset 1 down and 1 to the right

    Edit2

    Here is thw working code I came up with

    Please Login or Register  to view this content.
    For some reason , I don't understand why , but first Col and first Row in the array becomes empty ???

    I manged getting it working tweaking it a little but would have been nice knowing why this strange behavior happens.


    But anyway thanks for help , my code was finished looping trough 420 rows x 26 Col in less then a sec , which is quite nice
    frederik
    Last edited by colddeck84; 09-20-2017 at 07:22 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Redim array - out of range
    By michson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-30-2015, 03:51 AM
  2. [SOLVED] Subscript out of range - ReDim Preserve an array
    By dejavision in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-13-2014, 04:29 PM
  3. Using the ReDim array function in a multidimensional array in excel
    By Doruli in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2012, 01:43 PM
  4. [SOLVED] Redim Preserve array: subscript out of range
    By supern0va in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-02-2012, 12:58 PM
  5. redim array error....subscript out of range
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2011, 11:07 AM
  6. Assign sheet value to array... and redim the array size
    By Orange.CL in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2010, 07:18 AM
  7. dynamic array redim preserve: run-time error 9, Subscript out of range.
    By Broesel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2009, 08:39 AM

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