.ColumnWidth taking Long Time

[MrExcel]

I have a VBA macro that is taking a fair amount of time to run. After checking a timer with various parts of code, I was amazed that the following single line of code is taking 3 seconds:

Cells(1, 1).Resize(, 19).ColumnWidth = Array(12, 12, 6, 6, 6, 6, 8, 8, 6, 6, 8, 8, 8, 8, 6.5, 6, 6, 6, 6, 6)

When we get to this code, the ScreenUpdating is False and DisplayPageBreaks is False. I am puzzled why this one line takes so long to run.

Any ideas?

Bill

 

[Jack in the UK]

Hi bill

I have fouind this also, simple add col will slow excel and delete just the same, regardless of the code used and rest of code i would say 2 - 3 seconds depending on versions win Excel and PC system, seem to matter little

So why?

OK as i understand

A col is a col of cells 1x 65536, we know this but remenber if you add a col the the last col ie col 256 goes ???? where??? try putting data in it and add a column excel should stop and give message about cant add as data -whatever-

But if this is not the case then all cell to right will have to be re address as B will become c and so on, thats the delay right there all that readressing.

OK now we undertsand why, jack has studid this and has a fix, sometimes will speed this as a guru VBA codes i wound post the scripts but try this select the data range that to become col+1 all cells row 1 to 256 or limited if you can and copy and destination that range then no new col is required you have created your own, and they rever to the new column as its name now, remeber lower code line taht call cells ill need editing as it will go wrong,

try a small sample, see if this helps.

I hope thats helped a small bit, if you need more info repost ill be glad to explain deeper.

 

[MrExcel]

Problem solved. It was the print driver slowing it down. When I switched to a new print driver, everything is fine.

Went from 3 seconds with a fax driver to 10 miliseconds with a Laserjet driver.

_________________
MrExcel.com Consulting
This message was edited by MrExcel on 2002-08-24 04:46

 

[Jack in the UK]

Hi Bill

I would never have tought you have drives issues on your system we all know that they are a pain, not that effect me.

Im all XP and bank up to date, i surf a network 250 user national dial in and 30 printers also, no problems, but i still get the delay i just put up with it, and the data i gave is spot on, sorry little help.

If you running E97 then its more so and things fall out of date and also if unsupport OP with MS then worse still.

I have few issue in XP i add, also patch the Office kit is good XP now has SP2, with adding SP1 some say speed is comprimised for additional security, as outlook is a boat with a hole in it, sinking fast.

Soory ive been little help but woth remembering what ive said, as if you psuh excel out of its box adding a simple rowor col does this things can sometime react oddly

Take care Bill