Combining all possible array combinations for each row

In the below comments I pretend that the data you are splitting is comma-delimited. I did that simply because it is easier to show examples using commas than using tabs. The code included is still using tab as the delimiter.

If I understand your problem correctly, when you have a value of (for instance) "1,4,67" in column Q, your code is correctly generating extra rows - one with "1" in column Q, one with "4" in column Q, and one with "67" in column Q, with all other columns copied from the original line.

However, when you have a second cell with multiple comma-separated values, say "A,B" in column T, that "A,B" is still appearing on each of the first two generated rows, and is only being split on the third row - thus creating a total of 4 rows. But you want six rows generated instead (one for each value of "A,B" with each value of "1,4,67").

I'm also assuming you are calling the subroutine for each row in the original data.

The following code will process a line, expanding each of the values so that you get every combination:

Sub combo(x As Integer, splitCell As Boolean, lastcol As Long)
    Dim cellArray() As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim t As Long
    Dim y As Long
    Dim a As Long
    Dim cellValue
    Dim dstRow As Integer
    Dim srcRow As Integer
    Dim aCellWasSplit As Boolean

    srcRow = x
    dstRow = x
    splitCell = False
    Do While srcRow <= dstRow
        aCellWasSplit = False
        For y = lastcol To 2 Step -1
            cellValue = ws.Cells(x, y).Value
            cellArray() = Split(cellValue, Chr(10))
            Debug.Print cellValue
            If UBound(cellArray()) > LBound(cellArray()) Then
               Debug.Print "Splitting " & x, y
               aCellWasSplit = True

               For t = UBound(cellArray()) To LBound(cellArray()) Step -1

                    dstRow = dstRow + 1
                    Rows(dstRow).EntireRow.Insert

                    For a = lastcol To 1 Step -1
                        If a = y Then
                            ws.Cells(dstRow, a).Value = cellArray(t)
                        Else
                            ws.Cells(dstRow, a).Value = ws.Cells(srcRow, a).Value
                        End If
                    Next a
               Next t
               Exit For
            End If
        Next y
        If aCellWasSplit Then
            ws.Rows(srcRow).EntireRow.Delete
            dstRow = dstRow - 1
            splitCell = True
        Else
            srcRow = srcRow + 1
        End If
    Loop
    x = dstRow + 1
End Sub

And the following code can be used to test it:

Sub test()
    Dim anythingProcessed As Boolean
    Dim currentRow As Integer
    Dim lastCol As Long
    currentRow = 1
    lastCol = 5
    Do While currentRow <= ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        combo currentRow, anythingProcessed, lastCol
    Loop
End Sub