Speed problem

[Dickison]

I download a delimited .txt report from a SQL database that consists of approximately 200 columns and 5000 rows. In 125 of the columns I have nothing but numeric data or blanks. I put this report from two different dates on separate worksheets. I then replace all the blanks with zero's. There are two problems I encounter. If I do the replacement with only one worksheet in the file it replaces about 5000 rows x 1 column every several seconds so it takes a few minutes for the entire worksheet. If I have two worksheets in the file it replaces only about 20 rows x 1 column every several seconds, so it takes hours to replace everything in the worksheet. The other problem is that sometimes even with one worksheet it starts out fast but then slows to a crawl and I have to stop the process, save the file, and start again where I left off, but it never does get back to the fast speed. I have a Dell 450MHz with 256MB. I can switch back and forth between the file that is fast and the file that is slow to see if something is happening in the memory, thinking that if it was just memory it would affect both the same. Files are not shared. Any idea what's going on and how to speed it up?

 

[Aladin Akyurek]

Just curious: Why do you need blank/empty cells to fill in with 0's?
This message was edited by Aladin Akyurek on 2002-10-03 15:12

 

[Andrew Poulsom]

What method are you using to replace the blanks with zeroes?

 

[Dickison]

The 125 columns are options on product orders. I concatenate the 125 columns to do comparisons with earlier reports and I need to know how many orders consist of a given set of options. So, for example, how many of the 5000 orders consist of an option string '001011100101100110...'. How many orders were changed from last week's report?

The replacement is done by CTRL-H, leaving the "Find What?" blank and putting a '0' in "Replace With"

 

[Andrew Poulsom]

Try this method:

1. With cell A1 selected hold down Shift and press End then Home - now all cells are selected.
2. Press F5, click Special and choose Blanks.
3. Type 0 and press Ctrl+Enter.

Any quicker?

 

[Dickison]

Hey, that's really cool. Worked a lot faster.