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]

OK, that is a big step forward. The problem is that the script is not - I don't know a better way to say it - clearing itself out. All of the resultant cells that have not yet been touched by this new automated script are between 6 and 9 thousand characters. The first 5 are 7k, 14k, 21k, 28k then 32767 and that's where it crashes. So each previous iteration is now being added to the next.

Now to figure out why and how to fix it...

 

[boznian]

Yep, that's the problem. So I guess the question is how do I 'clear' or 'release' the mystring value after each pass?

 

[Norie]

Are you now writing to a file, or are you still putting the results of the code into cells?

If you are still putting the results into cells then it might have nothing to do with 'clearing out' a string.

A string can hold 2 billion odd characters, a cell can hold 32767 - that's a bit of a difference.

 

[boznian]

Are you now writing to a file, or are you still putting the results of the code into cells?

If you are still putting the results into cells then it might have nothing to do with 'clearing out' a string.

A string can hold 2 billion odd characters, a cell can hold 32767 - that's a bit of a difference.

But the fact remains, the script is adding the value in myString each pass to the previous one. This obviously has to be fixed, and I am certain it is the cause of the lockup.

 

[Norie]

It shouldn't be an issue if you were to write the data straight to a text file.

Is that not an option?

 

[boznian]

Norie, I do appreciate your help but you sure seem intent on changing my business process!

Seriously, this is the way it needs to be done and it works just fine manually. All I need to do is just clear this string after each pass. Something like Set myString = ""?? Of course this doesn't work but I am still searching the help file to figure out how to do it.

 

[boznian]

Well I am completely stuck. I have searched Excel help, the board and the net and found everything you could ever want to know about strings except how to clear its value.

I have tried these and many variants:

Set myString = ""
Set myString = "null"
Dim myString As String

I tried assigning a blank cell to the string:
Range("G99") = myString

I am dead in the water...

 

[shg]

Try replacing this:

    For Each cl In Selection
        myString = myString & cl
    Next cl

With this:

    myString = Join(Application.Transpose(Range("L101:L285")), "")

 

[boznian]

I had already tried that from the suggestion in post #2 - same error.

 

[shg]

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

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