Very inefficient code because of different sizes of arrays, how do i shorten my code?

Hi guys,

I have just started working with VBA and since I like to work as efficient as possible, I was wondering how I could shorten the code below. For instance, now I am using a very similar code with all of the 4 select cases, this because I am using different sizes of arrays and I dont know how to do it in another way. I would like to use these bits without having to copy and then modify them slightly every time. Does anybody now a solution to this problem? In advance, thanks very much for looking at my problem!

Bram

Please Login or Register  to view this content.

I haven't been through this in detail, but there's no reason to assign values to the arrays until after you've decided which case of i you're processing. So, based on the value of i, could you not just populate the array you need and then all of the cases will be generic.

So, all the case statement is used for is to determine which cell to split - the array is always given the same variable name, and then you only need one occurrence of the loop.

Does that make sense?

Hi Andrew, thanks for the very fast reply. I totally see your point, great to have a quick clear view on my problem, I am going to try I out now!

Andrew and Norie, thanks very much, works perfectly!!