My formatting code takes forever - please enhance?

[nickmax1]

This code simply formats my sheet but it takes forever and a day to finish the job....is there a more effective way to write this?

(a lot of it has been taken from recording macros and using that code)

Please Login or Register  to view this content.

[ragulduy]

Not sure if it would make much difference, depends how big your named ranges are but you can change your loops such as:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[nickmax1]

that has sped it up a bit,

how do you change my loop to match your code

Please Login or Register  to view this content.

My attempt

Please Login or Register  to view this content.

doesnt work...(type mismatch error)

[ragulduy]

Ah, I guess you can't use it with the UCase function.

[JosephP]

if Account,AccountAPRP and Comment are not contiguous, using Application.proper(Range("Account,AccountAPRP,Comment").value) will convert them all to the values in the first range

[nickmax1]

JosephP

thanks you have just solved my brain scratcher, I didnt understand why that was happening!!!

have to revert back to my original (but very slow) code from the first post......any other ideas?

[JosephP]

you can loop the areas

Please Login or Register  to view this content.

for ucase you can't do that (and application.upper doesn't exist) so you would be better off using arrays

Please Login or Register  to view this content.

for instance

[JosephP]

untested of course but perhaps

Please Login or Register  to view this content.

[nickmax1]

Wow, you have reduced my macro running time from 25 seconds to 1 second!! Legend!

have some rep!