Excel VBA memory usage

Question

I'm trying to copy a large number of lines (20k to 65k) into new workbooks, and for some reason, assigning the value of the range I'm copying uses more memory than using the copy/paste buffer, which doesn't make any sense to me, unless I'm somehow doing this wrong.

This is the original code:

Public Const FIRSTSHEETTAB As String = "Sheet1"
' <snip>

Dim last_row As Long
Dim num_files As Long
Dim ps_rng As Range
' <snip>

Dim i As Long
Dim new_book As Workbook
Dim start_row As Long
Dim end_row
start_row = 2
For i = 1 To num_files
    Set new_book = Workbooks.Add
    end_row = start_row + max_lines - 1
    If end_row > last_row Then
        end_row = last_row
    End If
    With new_book
        .Windows(1).Caption = "PS Upload " & i
        With .Worksheets(FIRSTSHEETTAB)
            .Range("1:1").Value2 = ps_rng.Range("1:1").Value2
            .Range("2:" & max_lines).Value2 = ps_rng.Range(CStr(start_row) & ":" & CStr(end_row)).Value2
        End With
    End With
    start_row = end_row + 1
Next i

And what I had to do to get this working was change .Range("2:" & max_lines).Value2 = ps_rng.Range(CStr(start_row) & ":" & CStr(end_row)).Value2 to the following:

ps_rng.Range(CStr(start_row) & ":" & CStr(end_row)).Copy
.Range("2:" & max_lines).PasteSpecial

And I don't understand why this works where as the former code runs out of memory. I'd much rather not have to overwrite whatever is in the copy/paste buffer if I can help it.

What's causing just the simple assignment to run out of memory?

Maybe you could change your code to work only with the UsedRange of the sheet you're copying from: that way you're not copying 16k columns of what are likely mostly empty cells. As to why it works when you do a Copy - possibly Excel is smart enough to be able to automatically exclude unused cells. — Tim Williams
– Tim Williams, Commented May 12, 2014 at 20:17
At some point in time, I realized that a Destination can be specified with Range.Copy. I don't know why it didn't occur to me to just use this the first time. — supercheetah
– supercheetah, Commented Jun 2, 2014 at 18:51

Tim Williams · Accepted Answer · 2014-05-12 20:41:53Z

When you use Copy, Excel is smart enough only to copy the used part of the Copy range.

Eg. see below: granted this is looking at the "Text" version of what's on the clipboard, but that's pretty much what you're getting when you PasteSpecial

Sub Tester()

    ActiveSheet.Cells.ClearContents
    ActiveSheet.UsedRange 'reset sheet
    CheckCopy '>> 1

    ActiveSheet.Range("A1:J1").Value = "x"
    CheckCopy '>> 10

    ActiveSheet.Range("XFD1").Value = "x"
    CheckCopy '>> 16384

    ActiveSheet.Range("XFD1").ClearContents
    CheckCopy '>> 16384

    ActiveSheet.UsedRange 'reset sheet
    CheckCopy '>> 10

End Sub

Sub CheckCopy()
    Dim d As New DataObject, s As String
    ActiveSheet.Rows(1).Copy
    d.GetFromClipboard
    s = d.GetText
    Debug.Print "#Cols: " & IIf(Len(s) = 0, 0, UBound(Split(s, vbTab)) + 1)
End Sub

You don't get this optimization when you directly assign Value between two large ranges.

Felipe Rosa · Accepted Answer · 2014-05-12 21:15:31Z

Because you are assigning the value of 16.384 x 65.000 = 1.064.960.000 cells simultaneously and this is too much for Excel to cope with.

A better approach would be to restrict the desired range to copy using the last column that does have values. I do not recommend utilizing the UsedRange property because it does give some unwanted results sometimes when a cell far away was edited sometime ago.

Below there is a code example:

Public Const FIRSTSHEETTAB As String = "Sheet1"
' <snip>

Dim last_row As Long
Dim num_files As Long
Dim ps_rng As Range
' <snip>

Dim i As Long
Dim new_book As Workbook
Dim start_row As Long
Dim end_row
start_row = 2
'Obtaining last column of the desired range
lastColumn = ps_rng.Cells(1, ps_rng.Columns.Count).End(xlToLeft).Column


For i = 1 To num_files
    Set new_book = Workbooks.Add
    end_row = start_row + max_lines - 1
    If end_row > last_row Then
        end_row = last_row
    End If
    With new_book
        .Windows(1).Caption = "PS Upload " & i
        With .Worksheets(FIRSTSHEETTAB)
            .Range(.Cells(1, 1), .Cells(1, lastColumn)).Value2 = ps_rng.Range(ps_rng.Cells(1, 1), ps_rng.Cells(1, lastColumn)).Value2
            .Range(.Cells(2, 1), .Cells(max_lines, lastColumn)).Value2 = ps_rng.Range(ps_rng.Cells(start_row, 1), ps_rng.Cells(end_row, lastColumn)).Value2
        End With
    End With
    start_row = end_row + 1
Next i

Collectives™ on Stack Overflow

Excel VBA memory usage

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related