3

Good afternoon,

Receiving the out of memory error in my code, any suggestions to change to code?

Inputs from column A are transferred to 2 different cells.

Output 1: Concatenates all the data from column A with commas inserted - between each cell value Output 2: Concatenates all the data from column A with commas and quotes inserted - for every cell value

Thanks

Sub Inserting_Commas()
' Macro to insert commas at the end of end cell value & to convert values from rows to single column

Range("A2").Select

Dim lastRow As Long
Dim outputStr As String
Dim outputStr2 As String
Dim rownumber As Long

Sheets("Sheet1").Select
lastRow = Range("A" & Rows.Count).End(xlUp).Row

' Seperate the column A by Commas
outputStr = Range("A2")

For rownumber = 3 To lastRow
    outputStr = outputStr & "," & Range("A" & rownumber)
Next

Range("D2") = outputStr

' Seperate the Column with Quotes and Commas
Range("A2").Select

For rownumber = 2 To lastRow
    Range("B" & rownumber).Value = "''" & Range("A" & rownumber) & "'"
Next

' --------------------------------------

outputStr2 = "'" & Range("B2")

For rownumber = 3 To lastRow
    outputStr2 = outputStr2 & "," & Range("B" & rownumber)
Next

Range("D20") = outputStr2

End Sub
Improve this question
2
  • 1
    How many rows are we talking about? Commented Aug 5, 2016 at 14:53
  • I'm guessing you need to rethink this. An Excel cell can only store 32,767 characters. If you're running out of memory building 2 strings, there's absolutely no way they're going to fit into 2 cells. What is the end goal of your code? Commented Aug 5, 2016 at 15:06

3 Answers 3

Reset to default
1

Based on your comment under @George's answer that...

this code is only a partial step. I am using the output data and extract information from a Dashboard (internal system similar to SQL). The dashboard works similar to search engines. If I copy and paste the output cell into dashboard - I can segregate the required dataset.

...there is no reason to try to store the complete output in a cell inside Excel. An Excel cell can only store 32,767 characters, so your output is going to be truncated regardless of whether building the string runs out of memory or not.

Since you don't appear to need the result inside of Excel, just write it to a text file:

Sub Inserting_Commas()
    Dim fso As Object, outFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set outFile = fso.CreateTextFile("C:\Foo\bar.txt", True) 'Replace with your own path.

    With Sheets("Sheet1")
        Dim lastRow As Long, rownumber As Long
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For rownumber = 2 To lastRow
            outFile.Write "'" & .Cells(rownumber, 1).Value & "'"
            If rownumber <> lastRow Then outFile.Write ","
        Next
    End With
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks all, I shall definitely explore that option of transferring the data into a text file
1

As Comintern has stated, a cell can only hold just under 33,000 characters, when your concatenation tried to place more than this into a cell you will get the error.

The below code does a similar thing to what you had written (although there were some odd single quotes that didn't look right to me so I have removed them) but in less lines of code, it also outputs to a cell whenever the resultant output is over 31000 so as to not exceed the cell size limit.

Sub Sample()
Dim WkSht           As Worksheet
Dim Cl              As Range
Dim StrOutput       As String
Dim StrOutput2      As String
Dim LngRow          As Long

Set WkSht = ThisWorkbook.Worksheets("Sheet1")
    LngRow = 2
    For Each Cl In WkSht.Range("A2:A" & WkSht.Range("A" & Rows.Count).End(xlUp).Row)

        StrOutput = StrOutput & IIf(StrOutput = "", "", ",") & Cl
        StrOutput2 = StrOutput2 & IIf(StrOutput2 = "", "", ",") & "'" & Cl & "'"

        If Len(StrOutput) > 31000 Then
            WkSht.Range("D" & LngRow) = StrOutput
            WkSht.Range("E" & LngRow) = StrOutput2
            StrOutput = ""
            StrOutput2 = ""
            LngRow = LngRow + 1
        End If

        DoEvents
    Next
Set WkSht = Nothing

MsgBox "Done!"

End Sub

You could then run each of these output cells in your SQL query but I suspect you could benefit from looking at the whole process. That will be a very text heavy query and you may not be able to run it at all depending on the method used. They would be different questions though if they arise at all.

Improve this answer

3 Comments

A cell can hold more than 33 characters.
Yeah someone else pointed out that typo too, I can't change it till I'm at a computer in about an hour
@GaryEvans - Got it. Hope that was what you had in mind.
0

How many rows has your list?

The culprit is maybe that part: 

For rownumber = 3 To lastRow
    outputStr = outputStr & "," & Range("A" & rownumber)
Next

Here you are concatenating the string and making it bigger and bigger.

If you have a lot of rows, then you will run out of memory.

Improve this answer

3 Comments

It does work for a 100+ rows, However I have a large set of data possibly 10000+. I have to use this data in SQL to extract data from a different system. Is there an alternative?
@bik_sas - Are you creating an SQL text file, using ADO, or something else? It still isn't clear why you're trying to write the string you're building to a cell.
Hi all, this code is only a partial step. I am using the output data and extract information from a Dashboard (internal system similar to SQL). The dashboard works similar to search engines. If I copy and paste the output cell into dashboard - I can segregate the required dataset.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.