Excel Macro for parsing freezers or takes an incredible amount of time to run

I will be the first to admit i am not well versed in VBA and so this code is a kind of Frankenstein build which is not a good idea... It appears to work, it just freezes excel for about 9 minutes.. should it really take that long? or am i doing something wrong thats causing a lot of unnecessary run-time? any thoughts would be welcome.

BTW the goal of the code is to look at column A in a worksheet and break it down so that each distinct value in column A creates its own worksheet which then receives the Titles in Row 1 and any other rows for that distinct value.

Thanks in advance

Please Login or Register  to view this content.

(source)

I'm not sure what Frankensteining you're referring to, it appears the code is as originally published with a few lines commented out (and the authorship removed).

Anyway, you're not the first person to mention their Excel does this. When I run this on mine and my test sheets, it's very fast, but occasionally people's workbooks appear to "lag" on the creation of the sheets. I'm going to ping some others to try it out.

Can you post your workbook so we can see the problem in action? Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Thanks.

Hi @jindon,

Very nice code. Couple of quick questions:

1) Would "dic(e) = Empty" would be the same as "dic.item(e) = Null". In other words add the key if it is not present.
2) I have never seen the approach of using

Please Login or Register  to view this content.

as I have always assumed you have to loop through the keys. In essence is this the same?

Thanks.

Thanks. Always like to find ways to streamline the code.

I would regard elision of default properties as more obfuscation than streamlining.