I want to do a combination in the scope excluding empty cells in the VBA Range area.

[ted kim]

xDRg1, xDRg2, xDRg3 Ranges contain empty Cells. Can I do a combination other than this?

Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg  As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("a2", ActiveSheet.Range("a2").End(xlDown))  'First column data
Set xDRg2 = Range("b2", ActiveSheet.Range("b2").End(xlDown))   'Second column data
Set xDRg3 = Range("c2", ActiveSheet.Range("c2").End(xlDown))   'Third column data
xStr = "_"   'Separator
Set xRg = Range("E2")  'Output cell
For xFN1 = 1 To xDRg1.Count
    xSV1 = xDRg1.Item(xFN1).Text
    For xFN2 = 1 To xDRg2.Count
        xSV2 = xDRg2.Item(xFN2).Text
      For xFN3 = 1 To xDRg3.Count
        xSV3 = xDRg3.Item(xFN3).Text
        xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
        Set xRg = xRg.Offset(1, 0)
       Next
    Next
Next
End Sub

 

[ted kim]

I need to make a combination excluding the empty cells.

 

[bebo021999]

Sub ListAllCombinations()
Dim i&, j&, k&, t&, lrA&, lrB&, lrC&, rng, arr(1 To 100000, 1 To 1)
lrA = Cells(Rows.Count, "A").End(xlUp).Row
lrB = Cells(Rows.Count, "B").End(xlUp).Row
lrC = Cells(Rows.Count, "C").End(xlUp).Row
For i = 2 To lrA
    For j = 2 To lrB
        For k = 2 To lrC
            If IsEmpty(Cells(i, "A")) Or IsEmpty(Cells(j, "B")) Or IsEmpty(Cells(k, "C")) Then
            Else
                t = t + 1
                arr(t, 1) = Cells(i, "A") & "_" & Cells(j, "B") & "_" & Cells(k, "C")
            End If
        Next
    Next
Next
Range("E2").Resize(t, 1).Value = arr
End Sub

 

[ted kim]

A value can exist in columns a and b, but an error occurs when column c does not have a value.
Can you supplement this?

I want to make the result value like column F