Hello all. I need to know what I can do to speed up my code. The following code runs in 12 seconds if the file is .xls but when converted to xlsm, it takes 5 min and 10 seconds to process 18 columns and 12 rows of data.
Thanks,
Please Login or Register to view this content.
Andrew
Last edited by drewship; 08-31-2010 at 10:05 AM.
As posted, that code should not compile or run, in any version of Excel...
Everyone who confuses correlation and causation ends up dead.
If the same code runs so much faster in Excel 2003 then I'd suspect that, at some point, it's looping through all of the rows on the sheet - and it's a lot quicker to loop through 65,000 rows than it is to go through 1,000,000 rows.
The compile problem seems to be caused by some extra & and " that you don't need when constructing file paths and names.
I notice you're using select a few times throughout the code. This is almost never necessary and can really slow things down.
Other than that I'd pop a few break points in your code and see which bits are causing the bottlenecks.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
You also refer to a non-existent Col() property
It should be faster to use:
rather than replacing cell by cell, and your use of xlDown rather than xlUp could cause what Andrew mentioned (i.e. looping through every single row).Please Login or Register to view this content.
Thanks for the replies. It appears I did not edit the posted code correctly when I tried to change from a server location to the C drive. I think that is corrected below. I will try to make the changes everyone indicated but I am not sure they address the overall question of why .xlsm is slower than .xls. Hopefully they will correct this issue since I would rather use .xlsm files. I have many other files that are affected as well and they are much larger files so this is not an isolated instance.
Thanks,Please Login or Register to view this content.
Andrew
I think the problem is at the start of the DeleteBlankRows and DeleteBlankColumns routines:
In the first line of the sub you're selecting all of the cells on the sheet, then you're setting the range to loop through to all of the selection.Please Login or Register to view this content.
So this will evaluate more than 16,000 columns (compared to 256 in Excel 2003) and more than 1,000,000 rows (compared to 65,000) - hardly surprising it's slower.
Romperstomper, I am not sure what Col() properity is non-existant but I am trying to use your code and I get "Expected: end of statement" with "XS" hilighted.
Please Login or Register to view this content.
Thanks Andrew-R...I can see where this may slow things down and will change it to see the effect.
Andrew
You don't need to loop through the cells using replace. It would be more like:
Please Login or Register to view this content.
Dom
in your DeleteBlankColumns sub you have this:Originally Posted by drewship
but there is no Col() property.Please Login or Register to view this content.
that's not my code. There is no loop in my code - it's just inside the With Sheets...End With block:I am trying to use your code and I get "Expected: end of statement" with "XS" hilighted.
Please Login or Register to view this content.
Please Login or Register to view this content.
Sorry romperstomper and Domski...I was distracted and sent before I could proofread and seperate my questions. I wasn't sure how to make the change in the code so I tried a couple things but never removed the loop. I incorporated both your code and limited the rows and columns being processed that Andrew-R indicated could be a bottleneck. I also removed the unreferenced Col() and Row() statements with no ill effect and now the code processes in less than a second in .xlsm!! Thanks everyone!!
Andrew
Last edited by drewship; 08-31-2010 at 10:09 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks