"Quizarate" wrote in message
...
I have an array in VBA with about 10,000 lines of data, which I am writing
to a text file. I can do this with no problem. However, before I write the
array to the text file, I want to delete lines that have certain values in
them. An example of the code I am playing with is he

For i = UBound(ForecastDataArray, 1) To 1 Step -1
If ForecastDataArray(i, 4) = "0000000" Then
ForecastDataArray.row(i).Delete
End If
Next i

I know this won't work, but I wanted to give people an idea of what I am
trying to do. If 0000000 is found in row i, column 4, then I want to delete
the entire row from my array. I know I could write the array to Excel, do
my search there, delete the lines out, then save the sheet as a text file,
but in the future, the array is going to have well over 200,000 lines in it,
so I won't be able to write it to excel.

Any help or suggestions are appreciated.

TIA,

Quiz

You may want to rethink this plan

I dont think you'll be able to allocate enough memory in VBA
to cope with 200,000 lines of text as a single VBA Array

The problem is that each VBA character takes 10+1 bytes of memory
and if you have only 10-15 chars per string thats around 2 kb per line

200,000 lines would be pushing 400,000 kb of memory
and I think thats beyond anything VBA can allocate

Its also going to be slow as hell since what you do
when you allocate a variable is give VBA a chunk of meory
to play with and when you do a search and replace it has to
start at the top of the memory block and look down


it until it finds the entry it wants

It sounds to me like you need to look at moving to a database
type solution , using ADO recordsets persisted in a jet database
may be a better option. At least then you have can goto the
next record each time without having to search from the top.

To answer your original question the best way to this is to filter
the original array into a new array

See
http://msdn.microsoft.com/library/de...ringarrays.asp

Keith

Hi Keith

Thanks for your response. I think that I will transfer the contents of the first array to a second one. It seems like it might be the easiest way

As for the memory issue, I didn't have one. Even when processing the largest groups of files, the most memory Excel was using (according to the Task Manager), was about 180,000K. This was for a 2D array having 16 elements in one dimension, and over 340,000 in the other. I was pretty suprised myself. All told, my subroutine processed over 2,000 unusable excel files into 41 tables I've linked into a databae. The only issue I'm still having is the fact that unwanted rows of data are pulled in also

Thanks for the help

John