The code below was provided to me by a user on the forum. It takes inputs from columns and creates combinations of them (I've also attached the file).
It uses the .CurrentRegion function - however, I'm missing information in column B, so if I start in column A, it'll ignore C and D.
How would I modify the code such that it ignores the column with missing information and skips directly to the next column?
Thank you!!
Option Explicit
Sub ChineseMenu()
' shg 2012
' Choose one from col A, one from col B, ...
Dim wks As Worksheet
Dim avInp As Variant ' ragged input list
Dim nCol As Long ' # columns in list
Dim rOut As Range ' output range
Dim iCol As Long ' column index
Dim aiCum() As Long ' *** count of arrangements from right to left
Dim aiCnt() As Long ' count of items in each column
Dim iArr As Long ' arrangement number
Set wks = ActiveSheet
With wks.Range("A1").CurrentRegion
.Style = "Input"
avInp = .Value
nCol = .Columns.Count
Set rOut = .Resize(1).Offset(.Rows.Count + 1)
.Offset(.Rows.Count).Resize(Rows.Count - .Rows.Count).Clear
End With
ReDim aiCum(1 To nCol + 1)
ReDim aiCnt(1 To nCol)
aiCum(nCol + 1) = 1
With WorksheetFunction
For iCol = nCol To 1 Step -1
aiCnt(iCol) = .CountA(.Index(avInp, 0, iCol))
aiCum(iCol) = aiCnt(iCol) * aiCum(iCol + 1)
Next iCol
End With
For iArr = 1 To aiCum(1)
For iCol = 1 To nCol
rOut(iArr, iCol) = avInp((Int((iArr - 1) * aiCnt(iCol) / aiCum(iCol))) Mod aiCnt(iCol) + 1, iCol)
Next iCol
Next iArr
rOut.Resize(aiCum(1)).Style = "Code"
End Sub
Chinese Menu.xlsm
Bookmarks