Make a macro bigger than 64k

[unolax]

I need a macro to go through over 5000 files and seperate thoese files into 20-25 differend spreadsheets. I can do this upto a point but once i hit over 64k it wont work. Knowing that this has been a standard since the early office/windows 95 days. Is there a registry hack or anything with faster computers these days to make it read them?
any help would be great. Included is one I wrote for a smaller batch that seperates usually about 1200 files into 4 different spreadsheets.

[royUK]

Do you mean increase the number of Rows? If you do then you need to get Excel 2007

[unolax]

i'm using 2007 but when i hit a macro thats size as a saved txt doc is larger than 64k it breaks

[Phil_V]

Thats just plain scary.... what on earth are you doing with a single SUB that is over 64k? I'd look at breaking it down and seperating it across several modules if I was you!

It would seem there is a LOT of redundant code in there, many many many 'Select' statements and I am sure some of the code could be made to use loops with offsets, and thats just with a quick glance.

The way I see it you have 2 choices;

A) Go through your code, improve the code condensing it as you do so
B) Stay with the bloated code you have, but you will still need to divide it into seperate modules, and seperate functions, which I think will be difficult if you don't do 'A' first anyway.

[JBeaucaire]

You have MULTIPLE instances of "If DestTWO > Zero" and looking at what you do when it's true, it looks like you're doing the same thing over and over, just in different columns.

There's no reason you can't collect ALL of the various "If DestTWO > Zero" sections and put them all into a single grouping, or at least WAY reduce the number of them. For Example:

Please Login or Register  to view this content.

Please Login or Register  to view this content.