On Friday, June 26, 2015 at 8:30:13 PM UTC-4, Bruno Campanini wrote:
I have a list of integers (myrange)

ROW Numbers
1 1,2,3,8,10,14
2 21,26,34,45,60,66
3 8,18,19,23,45,46
4 21,36,38,41,48,49
n .................
Each row is ordered LeftToRight, ASC; n is in the order of 7000.

I have 6 numbers (ordered the same way)
21,26,34,45,48,60 (myrow)

I write all the combinations I can get from
myrow, they are 2^6 = 64 but excluding C6,0 they are 63:
6,1 = 6
6,2 = 15
6,3 = 20
6,4 = 15
6,5 = 6
6,6 = 1

Now I want to see how many times EACH combination out of the 63'
appears in myrange, and in what row.

The solution for the example is:
C6,1 = 8
C6,2 = 11
C6,3 = 10
C6,4 = 5
C6,5 = 1
C6,6 = 0

Somebody tried scanning any row of myrange 63 times but
it is a very much time consuming way (more than 1 hr).
I tried using queries and got a good time of some 3 minutes.

Any good idea using VBA without any support of queries?
(using queries my Excel 2013/64 bit is unstable and
crashes often).

Bruno

What does it mean for a combination to appear in myrange? You never defined it. I suspect you mean that it has to appear as consecutive items in some row -- though you never actually said that. In any event -- dictionaries are clearly the way to go. Include a reference to Microsoft Scripting Runtime in your project. For each nonempty combination of myrow, construct a key which looks like e.g. "26-45-48" and add it to the dictionary (with say "" as the value -- it doesn't matter). Then loop through your myrange row by row (after transferring it into a VBA array in one step to avoid the overhead of repeated use of things like range() or cells()). For each row, used nested for loops to construct all 21 possible keys corresponding to that row. When you construct a key -- check if it is in the dictionary. If it is - increment a counter. 21*7000 is only 147000. That many dictionary accesses should only take a few seconds. There is no reason that the sort of thing you describe should take any more than 10 seconds or so.