PDA

View Full Version : Copying down and out of memory problem



jofo
04-25-2007, 04:47 AM
I use large delimited text files in my work, anything up to 200 columns by 20,000 rows. I load the data into one worksheet in a workbook and then using formulae in the top row of a second worksheet in the same workbook do various conversions such as stripping out characters, merging cells from worksheet 1 etc. I then copy down the top row in worksheet 2, get rid of the formulae in it, then delete worksheet 1.

It's the copying down that results in the memory problems. Originally, I copied the top row then pasted it into the requisite number of rows all at once. Above about 8000 rows, this gave an out of memory error. I now use the attached code, copying one row at a time then getting rid of the formulae on the row I have just copied from. Although this works with aprox. 13,000 rows, it gets very slow as I hit what I assume to be the same memory problem


Any suggestions?


Sub CreateSS()
Dim Rowcount As Integer, ColCount As Integer, c As Range, count As Integer, StatBarText As String

Application.ScreenUpdating = False
Sheets("TempMerged").Select
If Range("Assessment") = True Then StatBarText = "% of Assessment Records Created" Else StatBarText = "% of Merged Records Created"
Rowcount = Range("a1").CurrentRegion.Rows.count - 1
Sheets("Merged").Select
ColCount = Range("A7").CurrentRegion.Columns.count
Set c = Range("A8")
For count = 0 To Rowcount
Application.StatusBar = Round((count / Rowcount) * 100, 0) & StatBarText
Range(c.Offset(count, 0), c.Offset(count, ColCount)).Copy Destination:=c.Offset(count + 1, 0)
Range(c.Offset(count, 0), c.Offset(count, ColCount)) = Range(c.Offset(count, 0), c.Offset(count, ColCount)).Value
Next count
Range(c.Offset(Rowcount + 1, 0), c.Offset(Rowcount + 1, ColCount)) = Range(c.Offset(Rowcount + 1, 0), c.Offset(Rowcount + 1, ColCount)).Value
Application.StatusBar = ""
End Sub

JimmyTheHand
04-25-2007, 07:55 AM
Hi jofo :hi:, welcome to VBAX!

Unfortunately, at the moment I have no Excel available, and can't test my own suggestion, but I think copying down formulas should be done with AutoFill. (The same way as Excel does it, when you pull down the small black cross.)

As for getting rid of the formulas, use something like this:

Activesheet.Cells.Copy
Activesheet.Pastespecial Paste:=Values

Jimmy

jofo
04-25-2007, 08:24 AM
Hi jofo :hi:, welcome to VBAX!

Unfortunately, at the moment I have no Excel available, and can't test my own suggestion, but I think copying down formulas should be done with AutoFill. (The same way as Excel does it, when you pull down the small black cross.)

As for getting rid of the formulas, use something like this:

Activesheet.Cells.Copy
Activesheet.Pastespecial Paste:=Values

Jimmy

I tried autofill and that "slows down" quicker than the present method that I use.

Brandtrock
04-26-2007, 12:38 AM
I use large delimited text files in my work, anything up to 200 columns by 20,000 rows.
Ok.


I load the data into one worksheet in a workbook and then using formulae in the top row of a second worksheet in the same workbook do various conversions such as stripping out characters, merging cells from worksheet 1 etc. I then copy down the top row in worksheet 2, get rid of the formulae in it, then delete worksheet 1.
Since you don't save the original data in your final product, why not perform the various manipulations by VBA rather than through worksheet functions that require the additional overhead of cutting and pasting?

Obviously, without seeing what formulae you are using for your manipulations, writing code is next to impossible. Posting an example of your worksheet (just a couple of sanitized rows would suffice) might help get you pointed in the right direction.


It's the copying down that results in the memory problems. Originally, I copied the top row then pasted it into the requisite number of rows all at once. Above about 8000 rows, this gave an out of memory error. I now use the attached code, copying one row at a time then getting rid of the formulae on the row I have just copied from. Although this works with aprox. 13,000 rows, it gets very slow as I hit what I assume to be the same memory problem.

You could always try crying on your bosses shoulder for an upgrade to your machine.


Regards,

Paul_Hossler
04-26-2007, 06:31 PM
Something else to try. Without knowing exactly what you need to do, this is a guess, but the technique might be useful as a starting point for you to customize.

I guess that you basically want to move data from one sheet to another, processing it along the way :dunno

I tried to get rid of a lot of object referencing, and some things that seems overly complicated (again: guessing without all information), but the idea might prove useful.



Sub CreateSS()
Dim iRowcount As Long, iColCount As Long, N As Long

Application.ScreenUpdating = False

With Worksheets("TempMerged")
iRowcount = .Range("a1").CurrentRegion.Rows.count
iColCount = .Range("a1").CurrentRegion.Columns.count

For N = 2 To iRowcount
Call .Cells(N, 1).Resize(1, iColCount).Copy
Worksheets("Merged").Cells(N, 1).PasteSpecial paste:=xlPasteValues
Next N
End With

Application.StatusBar = False
End Sub