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
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