memory leak?

[gdesreu]

I've been using the following code for a few years to flip spreadsheet data around without issue. Recently however I received a large data set (about 5X larger than those I typically work with and the macro eventually runs out of resources and stops. Typically this runs instantly. I've tried it with screenupdating off and on and it has the same result along with various methods to clear the clipboard after the copy past sequences, they all have the same result. Obviously I have something wrong with my code. I can cut the data set in half and run it twice and it will work but I would prefer to know what is causing the out of resources crash. After a certain number of loops you can watch it get progressively slower each loop until eventually it crashes. Can someone tell me what I am doing wrong with the code below? Thanks

Sub flip()
'paste analytes
Do While Range("W2").Value > 0 
Dim LR
Range("s21:s73").Select 
    Selection.Copy
LR = Range("s" & Rows.Count).End(xlUp).Offset(1, 0).Select 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
 
    Range("w21:y73").Select
    Selection.Copy
    Range("s21").End(xlDown).Offset(-52, 1).Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
       
   Range("w2:w19").Select
    Selection.Copy
HDrng1 = Range("a21").End(xlDown).Offset(1, 0).Row 
HDrng2 = Range("s21").End(xlDown).Offset(0, -1).Row 
HDrange = "(a" & HDrng1 & ":" & "r" & HDrng2 & ")" 
Range(HDrange).Select 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=True
       Columns("w:y").Select 
    Selection.Delete Shift:=xlToLeft
    Range("w2").Select
    Loop
End Sub

 

[Joe4]

There are a few things you can do to speed up your code:
- Eliminate as many "Select" statements as you can. They are usually not necessary. Most every line that ends is "Select" and the next line begins with "Selection" can be combined
- Shut off ScreenUpdating in your code until the code finishes
- Put calculation on Manual until your code finishes
- Declare all your variables (otherwise, they default to Variant, which uses the most memory)

Here is an article which may help: https://www.soa.org/News-and-Publications/Newsletters/Compact/2012/january/com-2012-iss42-roper.aspx

I tried cleaning up your code a bunch.l See if this helps:

Sub flip()
'paste analytes

    Dim LR As Range
    Dim HDrng1 As Long
    Dim HDrng2 As Long
    Dim HDrange As String

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Do While Range("W2").Value > 0

        Range("s21:s73").Copy
        Set LR = Range("s" & Rows.Count).End(xlUp).Offset(1, 0)
        LR.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
 
        Range("w21:y73").Copy
        Range("s21").End(xlDown).Offset(-52, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
       
        Range("w2:w19").Copy
        HDrng1 = Range("a21").End(xlDown).Offset(1, 0).Row
        HDrng2 = Range("s21").End(xlDown).Offset(0, -1).Row
        HDrange = "(a" & HDrng1 & ":" & "r" & HDrng2 & ")"
        Range(HDrange).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Columns("w:y").Delete Shift:=xlToLeft
        
    Loop

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub

 

[Fluff]

Along with what Joe4 has said, you can avoid copying to the clipboard, like this

Sub flip()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
   Dim rw As Long
   Do While Range("W2").Value > 0
      Range("S" & Rows.Count).End(xlUp).Offset(1).Resize(53).Value = Range("s21:s73").Value
      Range("S21").End(xlDown).Offset(-52, 1).Resize(53, 3).Value = Range("w21:y73").Value
      rw = Range("A21").End(xlDown).Row
      Range("A" & rw + 1).Resize(Range("S21").End(xlDown).Row - rw, 18).Value = Application.Transpose(Range("w2:w19").Value)
      Columns("w:y").Delete shift:=xlToLeft
   Loop
Application.Calculation = xlCalculationAutomatic
End Sub

 

[gdesreu]

There are a few things you can do to speed up your code:
- Eliminate as many "Select" statements as you can. They are usually not necessary. Most every line that ends is "Select" and the next line begins with "Selection" can be combined
- Shut off ScreenUpdating in your code until the code finishes
- Put calculation on Manual until your code finishes
- Declare all your variables (otherwise, they default to Variant, which uses the most memory)

Here is an article which may help: https://www.soa.org/News-and-Publications/Newsletters/Compact/2012/january/com-2012-iss42-roper.aspx

I tried cleaning up your code a bunch.l See if this helps:

Sub flip()
'paste analytes

    Dim LR As Range
    Dim HDrng1 As Long
    Dim HDrng2 As Long
    Dim HDrange As String

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Do While Range("W2").Value > 0

        Range("s21:s73").Copy
        Set LR = Range("s" & Rows.Count).End(xlUp).Offset(1, 0)
        LR.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
 
        Range("w21:y73").Copy
        Range("s21").End(xlDown).Offset(-52, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
       
        Range("w2:w19").Copy
        HDrng1 = Range("a21").End(xlDown).Offset(1, 0).Row
        HDrng2 = Range("s21").End(xlDown).Offset(0, -1).Row
        HDrange = "(a" & HDrng1 & ":" & "r" & HDrng2 & ")"
        Range(HDrange).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Columns("w:y").Delete Shift:=xlToLeft
        
    Loop

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub

Thanks a lot, this is a lot cleaner! I tried it however and it still red X'd "Excel cannot complete this task with available resources. Choose less data or close other applications." Could it simply be too much data for VBA to handle? I've never hit that before doing something like this but I guess there is a first time for everything. The last row is 152,843 with 18 headers. I'm not exactly sure how to calculate if that is excels (2010) max or not. It almost finishes too! (about 20 samples left to flip.
I also get a system error &H80004005 (-2147467259) Unspecified error if I try to copy anything manually after it crashes but that's probably due to the above crash.

 

[Joe4]

That is a lot of data. Perhaps it is the copying to the clipboard which is problematic.
See if Fluff's suggestion helps.

 

[gdesreu]

That is a lot of data. Perhaps it is the copying to the clipboard which is problematic.
See if Fluff's suggestion helps.

Ill try fluffs suggestion now and post the results

 

[gdesreu]

Along with what Joe4 has said, you can avoid copying to the clipboard, like this

Sub flip()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
   Dim rw As Long
   Do While Range("W2").Value > 0
      Range("S" & Rows.Count).End(xlUp).Offset(1).Resize(53).Value = Range("s21:s73").Value
      Range("S21").End(xlDown).Offset(-52, 1).Resize(53, 3).Value = Range("w21:y73").Value
      rw = Range("A21").End(xlDown).Row
      Range("A" & rw + 1).Resize(Range("S21").End(xlDown).Row - rw, 18).Value = Application.Transpose(Range("w2:w19").Value)
      Columns("w:y").Delete shift:=xlToLeft
   Loop
Application.Calculation = xlCalculationAutomatic
End Sub

This is very slick as well. It seems to process slightly less data before it fails with "delete method of range class failed". (stopped at row 151789 as opposed to 152,843) However it did the processing much faster. Both approaches are worth me implementing over mine. Very slick and succinct. I know this is a large amount of data but it seems as if excel should be able to handle it. Ill defer to you guys though. I could split the data in half and everything will be fine, but I just wanted to make sure I have the most efficient code possible or that I'm not making glaring mistakes so I don't do the same thing on future tasks. - Thanks

 

[Norie]

Are you only processing data?

If you are then it might be an idea to use arrays.

 

[gdesreu]

Are you only processing data?

If you are then it might be an idea to use arrays.

Yes only data. Arrays are an even weaker point for me though. Of course I would be open to any ideas that work.

 

[offthelip]

Whoops sorry finger trouble , I didn't intend to post here!!