none
VBS failing on large amounts of data RRS feed

  • Question

  • Hello,

    I found this code from a site a while ago (so its not mine but I also cant give the author credit Sorry!) which works fine however when you have lots of data it either doesn't produce any results or it throws duplicate results into a random single cell. It works fine on a couple of thousand rows but much more than that it cant seem to handle. As the data I am running it against is 50k rows+ it just doesn't do anything.

    Does anyone have any idea why? I have a core i7 processor with 16GB of RAM.

    Sub ConcatenateCellsIfSameValues()
        Dim xCol As New Collection
        Dim xSrc As Variant
        Dim xRes() As Variant
        Dim I As Long
        Dim J As Long
        Dim xRg As Range
        xSrc = Range("L1", Cells(Rows.Count, "L").End(xlUp)).Resize(, 2)
        Set xRg = Range("H1")
        On Error Resume Next
        For I = 2 To UBound(xSrc)
            xCol.Add xSrc(I, 1), TypeName(xSrc(I, 1)) & CStr(xSrc(I, 1))
        Next I
        On Error GoTo 0
        ReDim xRes(1 To xCol.Count + 1, 1 To 2)
        xRes(1, 1) = "Name"
        xRes(1, 2) = "Data"
        For I = 1 To xCol.Count
            xRes(I + 1, 1) = xCol(I)
            For J = 2 To UBound(xSrc)
                If xSrc(J, 1) = xRes(I + 1, 1) Then
                    xRes(I + 1, 2) = xRes(I + 1, 2) & ", " & xSrc(J, 2)
                End If
            Next J
            xRes(I + 1, 2) = Mid(xRes(I + 1, 2), 2)
        Next I
        Set xRg = xRg.Resize(UBound(xRes, 1), UBound(xRes, 2))
        xRg.NumberFormat = "@"
        xRg = xRes
        xRg.EntireColumn.AutoFit
    End Sub

    Thanks

    James

    Monday, October 15, 2018 10:58 AM

All replies

  • Without the data to test, it is difficult to pin point the problem if it works with smaller amounts of data and not with 50k rows. However, I am inclined to think that Excel cannot handle the size of the array.

    Have a look at the following link where there is a reasonable explanation.

    https://stackoverflow.com/questions/7817427/excel-2007-vba-array-size-limit

    I suggest using a temporary worksheet in lieu of the array but expect it to be somewhat slower.


    Regards, OssieMac

    Monday, October 15, 2018 10:15 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Tuesday, October 16, 2018 7:52 AM
  • Thank you I have tried this on a 64bit install of Office but I still get the same issue unfortunately. 

    When you say: 

    "I suggest using a temporary worksheet in lieu of the array but expect it to be somewhat slower."

    What do you mean?

    Thanks

    James

    Tuesday, October 16, 2018 1:12 PM
  • "I suggest using a temporary worksheet in lieu of the array but expect it to be somewhat slower."

    What do you mean?

    Thanks

    James

    My understanding is that arrays are generally faster with processing than having the data on a worksheet because the array is all in memory but some processing time is taken writing to and retrieving data from a worksheet. My understanding is that data written to the worksheet does not rely on the amount of memory because it simply writes to the disk but I have to admit that I am not really conversant with how this all occurs behind the scenes and I am only really quoting what I have read.

    As per my suggestion, try the temporary worksheet and see what occurs.


    Regards, OssieMac

    Tuesday, October 16, 2018 7:49 PM
  • Hi SMBC4100,

    Have you tried Ossiemac's suggestion? How is the program running now? Do you have a better way to solve it?

    Best Regards,

    Bruce


    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.

    Thursday, October 18, 2018 11:04 AM
    Moderator