Running out of memory during script execution

[boznian]

I am running a script to capture data, format, concatenate and paste to new worksheet. The methods I am using have always worked for me in other scripts but in this one after a few iterations I get a crash with an Excel "out of memory" notice. My computer has 4GB of RAM and plenty left.

The offending code is this:

' create HTML output string

Range("L101:L285").Select
Dim cl As Range
Dim myString As String
For Each cl In Selection
myString = myString & cl
Next cl

It will work all day on single iterations or even a couple times through. How or what do I need to clear out? Or use a different variable type?

Thanks

 

[boznian]

I don't see that you tried that; I saw that you left the For loop wrapped around it. .

Yes, I had tried many, many different ways with that line of code.

Also, you need to specify that you want a null string as a separator; the default is a space.

Sorry, but this is going right over my head - I am not sure what you are referring to here...

 

[boznian]

Finally, I fixed it.

Added myString = ""
right after the copy and paste operation, before going to the next loop

So it ended up like this for anyone who cares...

Sub Macro83()
'
' Macro83 Macro
' Macro Created 03/29/11 by Mark
' Update and Publish All Item Descriptions
' FROM "BY MODEL" SHEET FOR YourWatchConnection.com

    Sheets("By Model").Select
    Range("B3").Select
    Application.ScreenUpdating = False
    MsgBox "Click OK to begin processing - the screen will not update"
    crow = ActiveCell.Row
    ' Skip check for blank row since this is first run
    GoTo FirstRun:
            
Do While ActiveCell.Value <> ""
' Get Model # from next row
    crow = ActiveCell.Row

FirstRun:   'for first iteration

' Set Variables
    Dim Brand As String
    Dim Model As String
    Dim Desc As String
    Dim Gender As String
    Dim List As String
    Dim Shape As String
    Dim Crystal As String
    Dim Clasp As String
    Dim Dial As String
    Dim Case1 As String
    Dim Case2 As String
    Dim CaseD As String
    Dim CaseT As String
    Dim BandM As String
    Dim BandL As String
    Dim BandW As String
    Dim BandC As String
    Dim Bezel As String
    Dim Crown As String
    Dim DateW As String
    Dim Special As String
    Dim Weight As String
    Dim Move As String
    Dim WR As String
    Dim Alarm As String
    Dim Jewels As String

' Populate variables
    Brand = ActiveCell.Offset(0, -1)
    Model = ActiveCell.Offset(0, 0)
    Desc = ActiveCell.Offset(0, 1)
    Gender = ActiveCell.Offset(0, 13)
    List = ActiveCell.Offset(0, 14)
    Shape = ActiveCell.Offset(0, 16)
    Crystal = ActiveCell.Offset(0, 17)
    Clasp = ActiveCell.Offset(0, 18)
    Dial = ActiveCell.Offset(0, 19)
    Case1 = ActiveCell.Offset(0, 20)
    Case2 = ActiveCell.Offset(0, 21)
    CaseD = ActiveCell.Offset(0, 22)
    CaseT = ActiveCell.Offset(0, 23)
    BandM = ActiveCell.Offset(0, 24)
    BandL = ActiveCell.Offset(0, 25)
    BandW = ActiveCell.Offset(0, 26)
    BandC = ActiveCell.Offset(0, 27)
    Bezel = ActiveCell.Offset(0, 28)
    Crown = ActiveCell.Offset(0, 29)
    DateW = ActiveCell.Offset(0, 30)
    Special = ActiveCell.Offset(0, 31)
    Weight = ActiveCell.Offset(0, 32)
    Move = ActiveCell.Offset(0, 33)
    WR = ActiveCell.Offset(0, 34)
    Alarm = ActiveCell.Offset(0, 35)
    Jewels = ActiveCell.Offset(0, 36)
    
    Sheets("~Publish to YWC~").Select
    Range("L3").Select
    
' Populate cells on Publish to YWC Page
    Range("L3") = Brand
    Range("L4") = Model
    Range("L6") = Desc
    Range("L24") = Gender
    Range("L7") = List
'    Range("L4") = Shape
    Range("L21") = Crystal
    Range("L11") = Clasp
    Range("L23") = Dial
    Range("L18") = Case1
    Range("L16") = Case2
    Range("L17") = CaseD
    Range("L19") = CaseT
    Range("L14") = BandM
    Range("L13") = BandL
    Range("L15") = BandW
    Range("L12") = BandC
    Range("L10") = Bezel
    Range("L20") = Crown
    Range("L22") = DateW
    Range("L28") = Special
    Range("L29") = Weight
    Range("L26") = Move
    Range("L27") = WR
    Range("L9") = Alarm
    Range("L25") = Jewels

    ' create HTML output string
    
    Range("L101:L285").Select
    Dim cl As Range
    Dim myString As String
    For Each cl In Selection
    myString = myString & cl
    Next cl

    Range("G99") = myString
    Range("G99").Select

    ' paste HTML to col AP of By Model
    Selection.Copy
    Sheets("By Model").Select
    Cells(crow, "AP:AP").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Application.CutCopyMode = False
    myString = ""       'the magic bullet
     
    GoTo Again:
    
Again:
    
    Sheets("By Model").Select
    Cells(crow, "B:B").Activate
    ActiveCell.Offset(1, 0).Activate

    Loop
    
Application.ScreenUpdating = True
MsgBox "Finished!"

End Sub

I am sure this could be cleaner and has fragments and bits from previous projects, but it works