Thread: Macro Array "Out of memory" any way around this?

Hey, the following macro is coming up with a debug error because I assume the data range im looking at is too big.

In the macro you can see rndTbl is looking at "wsList.Cells(3, "E").End(xlToRight).Offset(1)" in my sheet this goes across over 10,000 columns

Can I get around this anyway?

Sub TJUpdate()

    Static wsData As Worksheet:  Set wsData = Sheets("iR")
    Static wsList As Worksheet: Set wsList = Sheets("TJ")
	
    Dim rngTbl As Range:   Set rngTbl = Range(wsList.Cells(Rows.Count, "A").End(xlUp).Offset(, 4), wsList.Cells(3, "E").End(xlToRight).Offset(1))
    Dim arrTbl As Variant: arrTbl = rngTbl.Value
    
    Dim r As Long, c As Long, rr As Long, cc As Long, rrr As Long, ccc As Long
    Dim NameCell As Range
    Dim rngFound As Range
	
    For Each NameCell In Intersect(wsData.UsedRange, wsData.Columns("X"))
        If NameCell.Value <> "Name" And Trim(NameCell.Value) <> vbNullString Then
            Set rngFound = wsList.Columns("A").Find(What:=NameCell.Value, LookAt:=xlWhole)
            If Not rngFound Is Nothing Then
                r = rngFound.Row - 3
				c = wsList.Rows(3).Find(What:=NameCell.Offset(0, 1).Value, LookAt:=xlWhole).Column - 4
                arrTbl(r, c - 1) = arrTbl(r, c - 1) + 1
                If Trim(wsData.Cells(NameCell.Row, "P").Value) <> vbNullString Then arrTbl(r, c - 2) = arrTbl(r, c - 2) + wsData.Cells(NameCell.Row, "P").Value * 1
                Set rngFound = Nothing
            End If
        End If
    Next NameCell
	
    rngTbl.Value = arrTbl
    
End Sub

I still haven't been able to fix this, I don't know a solution around this error.

The error is thrown up on:

Dim arrTbl As Variant: arrTbl = rngTbl.Value

I'm not sure how you want me to run:

ubound(arrTbl)

I have looked into other ways of doing this but I still can't get it working

Bump as I still can't find a solution.

Hi

Perhaps you could explain what your ultimate aim is and why you need to create a such a mahoosive array. I think you will need an alternative. You've mentioned the total number of columns (over 10,000!!) but how many records (rows) are we talking?