Macro worked, but now crashes my workbook.

Had a macro built with this sites help and it worked perfectly. Until shortly ago. I haven't adjusted the code at all other than the name. The only thing i've done since working was share the workbook and lock it, but I have since unlocked it and unshared it.

Macro is as follows:

Please Login or Register  to view this content.

When I 'step into' the macro it breaks when I am on the ".Range("A4", "XFD1000000").ClearContents' and hit F8. That is when I get the spinning wheel.

I've never seen anyone actually use the last XFD column so that's a first!

My first thought is that whilst the original workbook was created with an Excel version > 2007 when the 16K columns were first introduced it has now been opened with a version of Excel prior to that which only has 256 columns. i.e. an .xls file

Why not change that line to

Please Login or Register  to view this content.

Hello taylorsm,

Formula:

Please Login or Register  to view this content.

That is a massive amount of Cells to run trough for any System!

Besides that, it will definitely crush on any System Running on Any Excel version prior to Excel 2007.

At best, please try the Code below. Maybe it will speed things up a bit.

Please Login or Register  to view this content.

Regards,

Building on Richard's and Winons replies:

if you replace the .clear contents line with:

Please Login or Register  to view this content.

It will do only as much work as there is to do.

Also if you put in the

Please Login or Register  to view this content.

remember to undo it afterwards:

Please Login or Register  to view this content.

Originally Posted by tony h

Building on Richard's and Winons replies:

if you replace the .clear contents line with:

Please Login or Register  to view this content.

Neat. An excellent suggestion

Do you mean replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

I tried this and it seems to work...

Please Login or Register  to view this content.

You said it crashed. Does it come up with an error message? Or are you, as before, getting the spinning wheel?


Also when you reply could you
1. report what happens if you change the line to

Please Login or Register  to view this content.

It won't clear the whole area but it would just check a few things.
2. report how big the file is and whether it seems over large. I suspect that the run of the code previously may have swollen the file which would cause the .usedrange to run slowly as well.

Hello taylorsm,

...the 10:42 post...

It is actually better to quote by Post Number, to avoid time differences across the Globe.

Regards.

Hi taylorsm,

Glad you finally managed to get your issue resolved.

Thank you so much for the "Rep"!

Regards

That's it, my man!!!

I am glad it worked for you just a couple of suggestions: one cosmetic and one functional

Please Login or Register  to view this content.

with this:
Notes:
1. the cosmetic element is changing the position of some of the code
2. the functional is to ensure that the code uses the named sheet Result. This could be done by changing ActiveSheet to Sheets("Result") or as I have here by letting the .UsedRange pick up the sheet from the with clause.

Please Login or Register  to view this content.