Failing to skip empty cells in a VBA Combination creator-CodePudding

I am not too experienced in VBA but found a code online that creates combinations in an excel sheet. This is great except, I need a rule in there that it should skip cells in the combination generator when empty. I tried a couple setups but it kept giving me the same result over and over.

So if i have the following table:

Table 1	Table 2
1	a
	b
3	c

The outcome should result in:

However, it leads to:

1-a
1-b
1-c
-a
-b
-c
3-a
3-b
3-c

Anyone can give me a tip or idea to see if this can be solved? Would love to know what is possible before investing too much time in it. Find the VBA below. Thanks in advance!

Sub CombinationGenerator()

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:A6")  'First column data
Set xDRg2 = Range("B2:B2")  'Second column data
Set xDRg3 = Range("C2:C2")  'Third column data
xStr = "-"   'Separator
Set xRg = Range("E2")  'Output cell

'Creating combinations
For xFN1 = 1 To xDRg1.Count
    If Cells(xFN1, "A") <> "" Then 'Ignore empty cells
    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
    End If
Next
End Sub

CodePudding user response：

xFN1 iterates from 1 to xDRg1.Cells.Count but the first row of xDRg1 is 2. So when you rebuild the range during the line Cells(xFN1, "A") you're putting 1, 2, 3 instead of 2, 3, 4 for the row numbers.

To avoid confusing code like this, I would suggest switching the For loop to a For Each loop using the Range.Cells collection, meaning the loop element would be a Cell (Range Object) instead of a Row number.

Sub CombinationGenerator()

Dim xDRg1 As Range, xDRg2 As Range, xDRg3 As Range
Dim xRg  As Range
Dim xStr As String
Dim xFN1 As Range, xFN2 As Range, xFN3 As Range
Dim xSV1 As String, xSV2 As String, xSV3 As String
Set xDRg1 = Range("A2:A6")  'First column data
Set xDRg2 = Range("B2:B2")  'Second column data
Set xDRg3 = Range("C2:C2")  'Third column data
xStr = "-"   'Separator
Set xRg = Range("E2")  'Output cell

'Creating combinations
For Each xFN1 In xDRg1.Cells
    If xFN1 <> "" Then 'Ignore empty cells
        xSV1 = xFN1.Text
        For Each xFN2 In xDRg2.Cells
            xSV2 = xFN2.Text
          For Each xFN3 In xDRg3.Cells
            xSV3 = xFN3.Text
            xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
            Set xRg = xRg.Offset(1, 0)
           Next
        Next
    End If
Next
End Sub