none
Limitation on Resize RRS feed

  • Question

  • Hi,

    I wrote some code to compare 2 sheets and report the differences. Basically I wrote to loops, the first one to compare each individual cell, the second to output the result, cell by cell. The second loop was/is very time consuming when talking over > 500,000 cells. The advantage of doing it this way was that I could put the desired format in each cell. But my progressbar showed that it would take about some 100,000 seconds to complete.

    I know that updating the statusbar is time consuming but there is only one update per second and the time it is using to do this is of less importance than showing the progress. But of course, nobody will wait some 100,000 seconds to see the result, supposing there is one, not for sure at this part in the code.

    I thought putting the result in a variant an put that in a range would be much faster. It is, but I think there is a limitation on the Resize. Code:

    objCompWbk.Sheets(1).Range("A1").Resize(UBound(varOutput, 1), UBound(varOutput, 2)) = varOutput

    We are talking of 53,000 rows and 51 columns. Is there a workaround or should I really put each individual cell?

    Kind regards,

    JP

    Sunday, October 15, 2017 11:52 AM

Answers

  • Hi All,

    I've found the issue, it had nothing to do with the size of the array or memory but with the data I tried to compare. The cell in row 4702, column 51 started with "===" and caused the error. Replacing all "=" by nothing solved it.

    Of course, I've to review the code to catch this iso replacing the equal sign but at least I know now the reason.

    • Marked as answer by JP Ronse Wednesday, October 18, 2017 4:02 PM
    Wednesday, October 18, 2017 4:02 PM

All replies

  • As long as your computer has enough RAM, it should be possible to create an array of 53000 rows and 51 columns.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, October 15, 2017 2:14 PM
  • Hi JP Ronse,

    It seems that you have found a faster way to put result in a variant but get error when using Resize?

    What do you mean there is a limitation on the size? Did you get any error message or it is too slow for working? I tried to put 53000*51 value to a two-dimensional array with 53000 rows and 51 columns and then use it to resize the sheet. The code worked well and used about 3 seconds to resize the worksheet.

    Sub Test()
    
    Dim varOutput(1 To 53000, 1 To 51) As Variant
    
    For I = 1 To 53000
    
    For J = 1 To 51
    
    varOutput(I, J) = (I - 1) * 51 + J
    
    Next J
    
    Next I
    
    Start = Now
    
    Application.ScreenUpdating = False
    
    ActiveSheet.Range("A1").Resize(UBound(varOutput, 1), UBound(varOutput, 2)) = varOutput
    
    Application.ScreenUpdating = True
    
    MsgBox "Used Time : " & Format(Now - Start, "hh:mm:ss")
    
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, October 16, 2017 10:12 AM
  • Hi Terry,

    I tried similar code with 200,000 rows & 100 columns in an new workbook, working fine and fast.

    In my original workbook, it raises a 1004 error. The strange thing is that it is starting the output and fills about 4700 rows & 51 columns. A first thought was bad housekeeping of objects, other variants but this did not make a difference.

    I saw also in the task manager that the Excel (32 bit on 64 bit PC) memory usage was going up to above 600MB.

    Looking further.

    Monday, October 16, 2017 4:52 PM
  • Hi All,

    I've found the issue, it had nothing to do with the size of the array or memory but with the data I tried to compare. The cell in row 4702, column 51 started with "===" and caused the error. Replacing all "=" by nothing solved it.

    Of course, I've to review the code to catch this iso replacing the equal sign but at least I know now the reason.

    • Marked as answer by JP Ronse Wednesday, October 18, 2017 4:02 PM
    Wednesday, October 18, 2017 4:02 PM