Run Time Error '7'. Out of Memory when trying to run VBA code

Question

Basically what the code does is, it looks for duplicate values in rows and transposes the corresponding values to columns. The code works perfectly for small amount of rows. The problem is I've a lot of spreadsheets with 600,000+ rows of data. Now when I select all the data and run the code it gives me "Run Time Error '7': Out of Memory" error.

The line the debugger highlights is:

ReDim Preserve xArr1(1 To UBound(xArr1, 1), 1 To xArr2(1) + t - 1)

And here is the whole VBA code:

Sub ConvertTable()
Update 20150113
Dim xArr1 As Variant
Dim xArr2 As Variant
Dim InputRng As Range, OutRng As Range
Dim xRows As Long
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
xArr1 = InputRng.Value
t = UBound(xArr1, 2): xRows = 1
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 2 To UBound(xArr1, 1)
        If Not .exists(xArr1(i, 1)) Then
            xRows = xRows + 1: .Item(xArr1(i, 1)) = VBA.Array(xRows, t)
            For ii = 1 To t
                xArr1(xRows, ii) = xArr1(i, ii)
            Next
        Else
            xArr2 = .Item(xArr1(i, 1))
            If UBound(xArr1, 2) < xArr2(1) + t - 1 Then
                ReDim Preserve xArr1(1 To UBound(xArr1, 1), 1 To xArr2(1) + t - 1)
                For ii = 2 To t
                    xArr1(1, xArr2(1) + ii - 1) = xArr1(1, ii)
                Next
            End If
            For ii = 2 To t
                xArr1(xArr2(0), xArr2(1) + ii - 1) = xArr1(i, ii)
            Next
            xArr2(1) = xArr2(1) + t - 1: .Item(xArr1(i, 1)) = xArr2
        End If
    Next
End With
OutRng.Resize(xRows, UBound(xArr1, 2)).Value = xArr1
End Sub

Credit: I found the original VBA code from here.

Thank you for your help.

Just read MSDN: msdn.microsoft.com/en-us/library/w8k3cys2.aspx When you ReDim with preserve then you may only change the last dimension (not the first one). — Ralph
– Ralph, Commented Aug 17, 2016 at 20:31
@Sgdva Thank you very much for the link. I installed excel 64 bit and it works perfectly now. — leoxanigm
– leoxanigm, Commented Aug 18, 2016 at 17:43

Community · Accepted Answer · 2017-05-23 10:33:50Z

0

Solution:
Install Excel 64 bits, Excel 32 bits can't handle that amount of memory
Disclaimer
While this question basically has the same memory problem, it may be confusing what the real solution is (the one provided here). If you would like to discuss either or not this answer should be deleted, please comment below before down voting.

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Aug 18, 2016 at 17:52

Sgdva

2,8103 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Run Time Error '7'. Out of Memory when trying to run VBA code

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related