Search code examples
excelvbaout-of-memory

VBA throwing Out Of Memory error when copying ranges by value

I am working on processing a fairy modest table (around 7k rows) in Excel and encountered an error while simply copying data from one place to another in different files. The copying is done via the command

destCol.value = sourceCol.value

I made sure that both ranges have matching sizes and take just the data, not the entire column.

I am seeing a "out of memory" error on this line when the looping through columns come to a particular column that was recently added. The new column is a text column which is mostly empty, but can have sometimes up to 2k chars. What seems strange is that the task manager does not show a spike in memory usage, so I think maybe some temporary array for copying is too large for the operation to succeed?

Is there a way I can view how much memory is being taken up by the column in memory and what is the memory limit, why does that error occur in this example?

I can copy the range using the copy and pasteSpecial xlPasteValues command, but I would hope to understand what is happening in this case. (I would have hoped copying a 6k row by 1 col range would be doable)

EDIT: I have determined that the problem occurs only when copying between two files, if I try to copy between two columns in the same file there error does not appear. What's more I tried to read that into two lines...

arr = sourceCol.value
destCol.value = arr

... and when the issue appeared on the second line I tried changing the destCol to be a (appropriately sized) range on a entirely new sheet. The "Out of memory" error popped, but the column was partially copied over - after about 1920 rows pasted which had 516k chars combined.

Solution

  • I recently had same issue (got to this question hoping for an answer...)

    comments here helped find my issue: I found the problem line by realizing the data did copy up to the problem cell as your original post mentions

    I had a column formatted as date with a value of -29 that somehow got in, as soon as i removed that anomalous value everything started working no problem

    i believe that it is actually getting some sort of crazy "type mismatch" error but not able articulate the correct error.