VBA生成无重复6位数字组合需求及代码优化请求

Got it, let's tweak your existing code to generate 6-digit combinations where all elements are unique (no repeated numbers like "1 1 1 3 4 6"). The core issue with your original code is that it allows reusing the same number across positions—we need to add restrictions to the loops to prevent that.

Modified Code (Unique Combinations, Order Doesn't Matter) #

This version generates all unique 6-number groups from the 1-9 set (total of 84 combinations, calculated as 9 choose 6):

Sub Unique6DigitCombinations()
    Dim nums(): nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
    ' Calculate exact number of unique combinations to avoid wasted memory
    Dim totalCombos As Long
    totalCombos = Application.WorksheetFunction.Combin(UBound(nums) + 1, 6)
    Dim arValues() As Variant
    ReDim arValues(1 To totalCombos, 1 To 6) ' Match Excel's 1-based indexing
    
    Dim n1 As Integer, n2 As Integer, n3 As Integer, n4 As Integer, n5 As Integer, n6 As Integer
    Dim x As Long: x = 1
    
    ' Restrict each inner loop to start after the previous index (no repeats)
    For n1 = 0 To UBound(nums) - 5 ' Leave 5 numbers remaining for the other positions
        For n2 = n1 + 1 To UBound(nums) - 4
            For n3 = n2 + 1 To UBound(nums) - 3
                For n4 = n3 + 1 To UBound(nums) - 2
                    For n5 = n4 + 1 To UBound(nums) - 1
                        For n6 = n5 + 1 To UBound(nums)
                            ' Assign unique numbers to each column
                            arValues(x, 1) = nums(n1)
                            arValues(x, 2) = nums(n2)
                            arValues(x, 3) = nums(n3)
                            arValues(x, 4) = nums(n4)
                            arValues(x, 5) = nums(n5)
                            arValues(x, 6) = nums(n6)
                            x = x + 1
                        Next n6
                    Next n5
                Next n4
            Next n3
        Next n2
    Next n1
    
    ' Paste results to Excel starting at cell A1
    Range("A1").Resize(totalCombos, 6).Value2 = arValues
End Sub

Key Changes Explained #

• Loop Restrictions: Each inner loop starts at previous index + 1. This ensures we never pick the same number twice, since each index maps to a unique number in the nums array.
• Efficient Array Sizing: Instead of using a massive 1,000,000-row array, we calculate the exact number of combinations with Combin() to save memory.
• 1-Based Indexing: The array uses 1-based indexing to match Excel's cell structure, making it easier to paste results directly without offsets.

If You Need Permutations (Order Matters) #

If you want all unique 6-digit sequences where order counts (e.g., 123456 and 654321 are separate entries), the total count is 60,480 (9 permute 6). For that, you'd need a permutation-generating helper function, but the above code is optimized for combinations (the most common use case for "unique number groups").

内容的提问来源于stack exchange，提问作者Nactrem