15 hours processor time to delete my mistake :(. Multi-threaded deleting in vba?

I am working with 273 column X 16394 row excel sheet. I did something really stupid and i put the MAX() function in 26 of the columns. It only took me about 5-10 minutes to paste these functions into excel, but for some reason its going to take ~15 hours to delete it.

I successfully deleted one of the 26 columns and it took 36 minutes. I did some experimenting and I found that the first and last 1000 cells in a column only take 20 seconds to delete. But the 1000 cells in the middle of the column take 3 minutes to delete.

The file is 27 Mb so i can only upload a small portion. I highlighted the columns causing my grief.

Is there a simple way to get excel to use all 4 of my cores to delete these cells? I am a little bit of a newbie to vba so hopefully its not going to take more than 14 hours of effort to learn this.

And yes my excel is set to manual calculation.

Any suggestions or help is appreciated

-Daniel

BTW is there a way to disable undo tracking?

Originally Posted by motivef

BTW is there a way to disable undo tracking?

I figured it out. I had to regedit. It didnt help.

I did some reading and found that excel keeps a record of all linkages in a given sheet. So everytime I delete a =MAX() it is rebuilding this table. I am guessing multi threading is not going to speed up rebuilding this table. What I need is for excel to delete everything and then rebuild the table from scratch.

Can i at least get a "your so screwed" or maybe an insult or something? At least then i wont feel like i'm talking to myself.

49,152 cells complete, 376832 to go

I dont understand why there is so much animosity toward microsoft, i mean they must of done something right if there products are so ubiquitous. Excel has definitely served me well. And i kind of like Win7.

I have to go to a meeting, i will be back in ~45 min.

Hi Motivef
Maybe it will be easier not to remove the unwanted columns, but to copy the desired columns in a new tab (or book)?

Originally Posted by nilem

Hi Motivef
Maybe it will be easier not to remove the unwanted columns, but to copy the desired columns in a new tab (or book)?

You are totally correct, that is indeed way faster. However I am too lazy to recreate the 8 plots that reference the data.

BTW thanks for posting!!!

65536 cells complete, 360668 to go

Originally Posted by vandan_tanna

have you tried setting calculation to manual?

That should help.

Ya I tried that. It doesnt help. Thanks for the post anyway!

Originally Posted by JosephP

are you actually deleting the columns or just clearing the cells?

Thanks for the post!

I've tried:
clearing the cells
Pasting over the cells
Autofilling over the cells

All of these perform similarly

And yes deleting the column is a bad idea.

Here is my plan:
1) Write macro to paste over cells i dont want
2) Run said macro
3) Go home
4) Drink beer

the drink beer sounds like a good idea ....
I hope your problem is now solved.

This is a strange forum .. lots of readers .. not many suggesting solutions???

Hi Joesphp ..... not all forums are like that ... other forums I'm a member of have helps of people with good knowledge ready to help out a stuck newbie.

to the OP....

without understanding the full impact of your problem...

why couldn't you use the "replace" function in the tool bar to replace the MAX() function with something easier to handle??

I got network errors all day on Friday. Otherwise i would have responded sooner.

Originally Posted by shg

I put =1 in C10 and double-clicked to copy down, repeated that in M10, then cleared both columns. Took a total of about 3 minutes.

Thanks for the benchmark. I actually get about the same for file i posted. The 27 Mb file takes much longer. This makes sense if excel is rebuilding the formula linkage table over and over again. The more linkages on a sheet, the larger the table is.

The beer strategy worked. On Friday morning all the cells were updated. This made a huge difference in the general performance of working with my sheet.

---------- Post added at 09:24 AM ---------- Previous post was at 09:21 AM ----------

Originally Posted by JosephP

that's normal for this kind of forum in my experience. most people are either searching or asking questions-only a relatively small percentage actually answer.

I think in this case, there really is no easy solution. The slowness is caused by the way excel updates its formula linkage table. The only way to fix this is to rewrite excel.

hmmm.... This gives me an idea, I wonder if open office does any better with this problem? I cant test it because i dont have admin rights on my work computer.[COLOR="Silver"]