안녕하세요~


첨부파일로
이렇게 활용해보세요.

Public Sub 조합()

    Dim ws As Worksheet
    Dim arr1 As Variant, arr2 As Variant, arr3 As Variant
    Dim items1() As String, items2() As String, items3() As String
    Dim n1 As Long, n2 As Long, n3 As Long
    Dim i As Long, r As Long, c As Long
    Dim hRow As Long, tCom As Long
    Dim Arr() As Variant
    Dim arrRow As Long
    Dim clr As Long
    
    Set ws = ActiveSheet
    
    On Error GoTo ErrorHandler
    
    '// 1. 데이터 읽기 및 유효성 검사
    arr1 = ws.Range("B1:E1").Value
    arr2 = ws.Range("B2:G2").Value
    arr3 = ws.Range("B3:G3").Value
    
    '// 2. 빈 셀 제외하고 실제 항목만 추출
    items1 = ext_Ept(arr1)
    items2 = ext_Ept(arr2)
    items3 = ext_Ept(arr3)
    
    n1 = UBound(items1) - LBound(items1) + 1
    n2 = UBound(items2) - LBound(items2) + 1
    n3 = UBound(items3) - LBound(items3) + 1
    
    '// 데이터 검증
    If n1 = 0 Or n2 = 0 Or n3 = 0 Then
        MsgBox "입력 범위에 데이터가 없습니다!", vbExclamation
        Exit Sub
    End If
    
    tCom = n2 * n3  '// 한 매매방법당 조합 수
    clr = RGB(255, 230, 153)
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    '// 3. 기존 출력 영역 삭제 (9행부터 아래)
    If ws.Cells(9, 2).Value <> "" Then
        ws.Rows("9:" & ws.Rows.Count).ClearContents
        ws.Rows("9:" & ws.Rows.Count).Interior.ColorIndex = xlNone
    End If
    
    hRow = 9
    
    '// 4. 각 매매방법별로 블록 생성
    For i = LBound(items1) To UBound(items1)
        '// 4-1. 헤더 작성 (조합 수)
        ws.Cells(hRow, 2).Value = tCom
        ws.Range(ws.Cells(hRow, 2), ws.Cells(hRow, 2 + n2 - 1)) _
            .Interior.Color = clr
        
        '// 4-2. 조합 배열 생성 (메모리에서 작업)
        ReDim Arr(1 To n3, 1 To n2)
        
        For r = LBound(items3) To UBound(items3)
            For c = LBound(items2) To UBound(items2)
                Arr(r - LBound(items3) + 1, c - LBound(items2) + 1) = _
                    items1(i) & items2(c) & items3(r)
            Next c
        Next r
        
        '// 4-3. 배열을 한번에 시트에 쓰기 (성능 향상)
        ws.Range(ws.Cells(hRow + 1, 2), _
                 ws.Cells(hRow + n3, 2 + n2 - 1)).Value = Arr
        
        '// 다음 블록 위치 (헤더 + 조합 + 빈줄)
        hRow = hRow + n3 + 2
    Next i
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    MsgBox n1 & "개 매매방법 × " & tCom & "개 조합 = " & _
           n1 * tCom & "개 조합 블록 생성 완료!", vbInformation
    Exit Sub
    
ErrorHandler:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox "오류 발생: " & Err.Description, vbCritical
End Sub