VBA that can generate (Only the Report )

[Motilulla]

Using Excel 2010
Hello,

I have got VBA code which generate all possible combination form 4 sets under this link https://www.excelforum.com/excel-pro...ml#post5953979

Does is it possible…to have VBA code which can generate (Only the report) without generating all combinations as shown in the column I:M… (Only the report in column O:P) if once the report has generated I can chose min-max range easily…

Here is workbook attached with an example.

Please help.

Regards,
Moti

[JohnTopley]

A "quick fix": using Data Dictionary might (will) be slightly quicker.

Another example of not thinking clearly what is REALLY required!

Option Explicit

Sub demo1() 'By JohnTopley

Dim i As Long, ii As Long, iii As Long, iiii As Long, n As Long, j As Long, lr As Long
Dim a, b
Const srow As Long = 6

Application.ScreenUpdating = False

lr = Cells(Rows.Count, 3).End(xlUp).Row

a = Range("C" & srow & ":F" & lr)

ReDim b(1 To 30000, 1 To 5)


For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row - srow + 1
    For ii = 1 To Cells(Rows.Count, 4).End(xlUp).Row - srow + 1
        For iii = 1 To Cells(Rows.Count, 5).End(xlUp).Row - srow + 1
            For iiii = 1 To Cells(Rows.Count, 6).End(xlUp).Row - srow + 1
                n = n + 1
                b(n, 1) = a(i, 1): b(n, 2) = a(ii, 2): b(n, 3) = a(iii, 3): b(n, 4) = a(iiii, 4)
                For j = 1 To 4
                    b(n, 5) = b(n, 5) + b(n, j)
                Next j
            Next iiii
        Next iii
    Next ii
Next i


[I6].Resize(30000, 5).Clear
[I6].Resize(n, 5) = b
[I6].Resize(n, 5).Borders.Weight = 2
Columns(9).Resize(, 5).HorizontalAlignment = xlCenter

[M6].Resize(n, 1).Copy [O6]
[O6].Resize(n, 1).RemoveDuplicates Columns:=1, Header:=xlYes
n = Cells(Rows.Count, "O").End(xlUp).Row - 5
[O5].Resize(n + 1, 1).Sort key1:=[O5], order1:=xlAscending, Header:=xlYes

With [P6].Resize(n, 1)
.Formula = "=COUNTIF(M:M,O6)"
.Value = .Value
End With

Columns(15).Resize(, 2).HorizontalAlignment = xlCenter

[I1].Resize(30000, 5).Clear

Application.ScreenUpdating = True

End Sub

[Motilulla]

A "quick fix": using Data Dictionary might (will) be slightly quicker.

@JohnTopley, this work great! It recognizes the length of each group automatically. This is very helpful. I feel grateful for your kind help. Good luck.

Another example of not thinking clearly what is REALLY required!

I really accept my mistakes; I always think after I get answer for next step…next time I will be thinking all at once may be. Sorry for the inconveniences. This is solved 100%

My Best regards,
Moti