.ColumnWidth taking Long Time

Joined
Feb 8, 2002
Messages
3,417
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
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
 
Upvote 0
Solution
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
 
Upvote 0

Forum statistics

Threads
1,224,898
Messages
6,181,625
Members
453,058
Latest member
rmd0725

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top