Need a Concatenating VBA code to prevent memory issue workaround

Question

My set up: Have 50,000 rows of data. ( My row count will increase in the future. So might as well say I have a full worksheet of 64000+ rows.) All Data is TEXT, no formulas, etc.

Column A is open Columns B thru AC contain the Data that needs to be concatenated The Data in the rows once concatenated to Column A will contain 60,000 digits or 6kb in file size. After additional maniuplation each cell will become a file.

I have tried concatenating in Excel and I run into memory issues. The memory issue is when I Select and fill down the concatenating function into the worksheet. It crashes at the 8200 +/-row. My system is 2gb of ram, windows xp professional and Excel 2003. Have 4GB of disk space

Hoping to find a VBA code that will conserve memory, and not crash like it does in excel.

Thank you

Answer 1

Although this is an unusual use for excel, Assuming Each Concatenation operation takes memory just free up some memory before you run out.

Not that elegant, although you could do 2000 rows copy and paste your Column A over itself as values. Optionally also deleting A2:AC2002 (the used data which may not be needed). Turning off Automatic calculation may also help.

With Macro from the macro recorder simple record a macro of you doing this [relatively positioned]. Begin by selecting the formula you have done.

Very crude code:

Sub Macro5()

With Application
  .EnableEvents = False
  .ScreenUpdating = False
End With

Dim i As Long

For i = 0 To 32
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1:A2000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.Offset(-1, 0).Range("A1:A2000").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Offset(2000, 0).Range("A1").Select
Next i

With Application
  .EnableEvents = True
  .ScreenUpdating = True
End With

End Sub