ignoring blanks in a macro range

[excelNewbie22]

can anyone please tell me how to add a code line for this macro for ignoring empty cells?
in this line:

    Set NumbersPoolRange = Range("cn1:dg1")

Option Explicit
'
' Inspired by some previous code from pgc01 https://www.mrexcel.com/board/threads/combination-help.277924/post-1379220
'
Sub AllCombinationsV2()
'
    Dim StartTime                   As Double
    StartTime = Timer                                                                                               ' Start the stop watch
'
    Dim ArrayRow                    As Long
    Dim DrawAmount                  As Long, TotalCombinations      As Long
    Dim OutputRow                   As Long
    Dim PoolSize                    As Long
    Dim NumbersPoolRange            As Range
    Dim OutputColumn                As String
    Dim OutputHeader                As String
    Dim NumbersDrawnArray           As Variant, NumbersPoolArray    As Variant, OutputArray     As Variant
'
    Set NumbersPoolRange = Range("cn1:dg1")             ' <--- Set this to the range of the pool #s to use
    DrawAmount = "6"                                  ' <--- set the amount of #s to pick in this cell
    OutputColumn = "bd"                               ' <--- Set this to the column letter that you want to display results to
    OutputRow = 2                                     ' <--- Set this to the row # to start displaying the combinations
    PoolSize = NumbersPoolRange.Columns.Count         ' Get the amount of numbers in the pool
    OutputHeader = DrawAmount & " of " & PoolSize                                                                ' <--- Set this to the header for the output column
'
    Application.ScreenUpdating = False                                                                             ' Turn screen updationg off
'
    TotalCombinations = Application.WorksheetFunction.Combin(NumbersPoolRange.Count, DrawAmount)                    ' Get total # of combinations that will be produced
'
    NumbersPoolArray = NumbersPoolRange                                                                             ' Load pool of numbers to use into 2D 1 based NumbersPoolArray
'
    'Range("A3").Value = "Total # of combinations"                                                                   ' Display header of 'Total # of combinations'
    'Range("aa1").Value = TotalCombinations                                                                           ' Display # of combinations that will be generated
'
    ReDim NumbersDrawnArray(1 To DrawAmount)                                                                       ' Establish size of 1D one based NumbersDrawnArray
    ReDim OutputArray(1 To TotalCombinations, 1 To DrawAmount)                                                      ' Establish size of 2D one based OutputArray
'
    Call GetAllCombinations(NumbersPoolArray, DrawAmount, NumbersDrawnArray, ArrayRow, OutputArray, 1, 1)           ' Calculate the Combinations
'
    Range(OutputColumn & OutputRow - 1).Value = OutputHeader                                                        ' Display output header
    Range(OutputColumn & OutputRow).Resize(TotalCombinations, 1).Value = OutputArray                                ' Display results to sheet
'
    ActiveSheet.UsedRange.EntireColumn.AutoFit                                                                     ' Autofit all column widths on the sheett
'
    Application.ScreenUpdating = True                                                                               ' Turn screen updationg back on
'
    Debug.Print TotalCombinations & " combinations completed in " & Timer - StartTime & " seconds."                 ' Display the completion time in the 'Immediate' window ... CTRL+G in the VBE window
    MsgBox TotalCombinations & " combinations completed in " & Timer - StartTime & " seconds."                      ' Display the combination count & time to complete to the user
End Sub


Sub GetAllCombinations(ByVal NumbersPoolArray As Variant, ByVal DrawAmount As Long, ByVal NumbersDrawnArray As Variant, _
        ByRef ArrayRow As Long, ByRef OutputArray As Variant, ByVal NumbersPoolColumn As Long, ByVal DrawNumber As Long)
'
    Dim ArrayColumn         As Long
'
    For NumbersPoolColumn = NumbersPoolColumn To UBound(NumbersPoolArray, 2)                                        ' Loop through the NumbersPoolArray columns
        NumbersDrawnArray(DrawNumber) = NumbersPoolArray(1, NumbersPoolColumn)                                      '   Save NumbersPoolArray(1, NumbersPoolColumn) value to NumbersDrawnArray(DrawNumber)
'
        If DrawNumber = DrawAmount Then                                                                             '   If last DrawNumber of DrawAmount has been reached then ...
            ArrayRow = ArrayRow + 1                                                                                 '       Increment ArrayRow
'
            For ArrayColumn = 1 To DrawAmount                                                                       '       Loop through NumbersDrawnArray columns
                OutputArray(ArrayRow, 1) = OutputArray(ArrayRow, 1) & NumbersDrawnArray(ArrayColumn) & "-"          '           Save NumbersDrawnArray column value & delimiter to OutputArray
            Next                                                                                                   '       Loop back
'
            OutputArray(ArrayRow, 1) = Left$(OutputArray(ArrayRow, 1), Len(OutputArray(ArrayRow, 1)) - 1)           '       Remove the last delimiter '-' from the OutputArray string
        Else                                                                                                        '   Else ...
            Call GetAllCombinations(NumbersPoolArray, DrawAmount, NumbersDrawnArray, ArrayRow, _
                    OutputArray, NumbersPoolColumn + 1, DrawNumber + 1)                                             '       Recursive loop to get next value of NumbersDrawnArray
        End If
    Next                                                                                                            ' Loop back
End Sub

macro by johnnyL

 

[Alex Blakenburg]

Test this on a copy of your workbook. I have only made change to the first sub so that is what I have below:
the below assumes that PoolSize should only count the non-blank cells in cn1:dg1

Sub AllCombinationsV2()
'
    Dim StartTime                   As Double
    StartTime = Timer                                                                                               ' Start the stop watch
'
    Dim ArrayRow                    As Long
    Dim DrawAmount                  As Long, TotalCombinations      As Long
    Dim OutputRow                   As Long
    Dim PoolSize                    As Long
    Dim NumbersPoolRange            As Range
    Dim OutputColumn                As String
    Dim OutputHeader                As String
    Dim NumbersDrawnArray           As Variant, NumbersPoolArray    As Variant, OutputArray     As Variant
'
    Set NumbersPoolRange = Range("cn1:dg1")             ' <--- Set this to the range of the pool #s to use
    DrawAmount = "6"                                  ' <--- set the amount of #s to pick in this cell
    OutputColumn = "bd"                               ' <--- Set this to the column letter that you want to display results to
    OutputRow = 2                                     ' <--- Set this to the row # to start displaying the combinations
    
    ' ----- Modification
    Dim NumbersPoolArrayTemp As Variant
    Dim iPoolArray As Long
    NumbersPoolArrayTemp = NumbersPoolRange
    ReDim NumbersPoolArray(1 To 1, 1 To UBound(NumbersPoolArrayTemp, 2))
    For iPoolArray = 1 To UBound(NumbersPoolArrayTemp, 2)
        If NumbersPoolArrayTemp(1, iPoolArray) <> "" Then
            PoolSize = PoolSize + 1
            NumbersPoolArray(1, PoolSize) = NumbersPoolArrayTemp(1, iPoolArray)
        End If
    Next iPoolArray
'    PoolSize = NumbersPoolRange.Columns.Count         ' Get the amount of numbers in the pool - replaced by counter
    OutputHeader = DrawAmount & " of " & PoolSize                                                                ' <--- Set this to the header for the output column
'
    Application.ScreenUpdating = False                                                                             ' Turn screen updationg off
'
    TotalCombinations = Application.WorksheetFunction.Combin(PoolSize, DrawAmount)                    ' Get total # of combinations that will be produced
'
    ReDim Preserve NumbersPoolArray(1 To 1, 1 To PoolSize)
' ----- End of modification
    'Range("A3").Value = "Total # of combinations"                                                                   ' Display header of 'Total # of combinations'
    'Range("aa1").Value = TotalCombinations                                                                           ' Display # of combinations that will be generated
'
    ReDim NumbersDrawnArray(1 To DrawAmount)                                                                       ' Establish size of 1D one based NumbersDrawnArray
    ReDim OutputArray(1 To TotalCombinations, 1 To DrawAmount)                                                      ' Establish size of 2D one based OutputArray
'
    Call GetAllCombinations(NumbersPoolArray, DrawAmount, NumbersDrawnArray, ArrayRow, OutputArray, 1, 1)           ' Calculate the Combinations
'
    Range(OutputColumn & OutputRow - 1).Value = OutputHeader                                                        ' Display output header
    Range(OutputColumn & OutputRow).Resize(TotalCombinations, 1).Value = OutputArray                                ' Display results to sheet
'
    ActiveSheet.UsedRange.EntireColumn.AutoFit                                                                     ' Autofit all column widths on the sheett
'
    Application.ScreenUpdating = True                                                                               ' Turn screen updationg back on
'
    Debug.Print TotalCombinations & " combinations completed in " & Timer - StartTime & " seconds."                 ' Display the completion time in the 'Immediate' window ... CTRL+G in the VBE window
    MsgBox TotalCombinations & " combinations completed in " & Timer - StartTime & " seconds."                      ' Display the combination count & time to complete to the user
End Sub

 

[excelNewbie22]

thanks a lot alex!
works perfect!


i wonder if i'm not exaggerating to ask more .... while waiting to someone to helped,
i thought of another problem,
if you could help, it'll be huge...

is it possible to add a function to access the macro, so i can fill right, it'll will be saving me tons of time!!
something like
=GenComb(a1:s1,6,x2)
a1:s1 - range, 6=# of combinations, x2=results presented


if i'm exaggerating.... i'll wait sometime and post another thread

 

[Alex Blakenburg]

You might need to do that as a separate thread. I am not really understanding what you are trying to do and I think you will need some sample data to show the before and after position, in the new thread.

 

[excelNewbie22]

understood!
thanks a lot alex!