Split comma separated text into multiple columns

[lricher]

Hi All,
i have a VBA macro that allows me to extract ";" separated text into multiple rows. however, i reach the bottom of the excel sheet, ie row# 1048576 and, as a result, i get an error from excel.
how can i encapsulate my macro so that when i reach row# 1048000 the split continues on the ext column (and so on) until i reach the end of the data needed to be split?
my macro is as follows:

Sub TestSplit_V1()
'
Dim i, n, lastrow As Long
Dim j As Integer
Dim Chain As String
Dim Table() As String

lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'identify last row value

n = 1

    For i = 1 To lastrow
    Chain = Cells(i, 2).Value 'Select data to split from 2nd column
    Table() = Split(Chaine, ";") 'Split based on ";"
    For j = 0 To UBound(Table)
        Cells(n, 3).Value = Table(j) 'Writing data on 3rd column
        n = n + 1
    Next 'Next "i"

Next 'Next "n"
    
End Sub

Now i know i'm supposed to add another integer or Long value such as "h" for instance, but there is also a need to go back to the starting row.
i suspect something like Cells(n,h).value and n needs to go back to the value of n = 1 but not sure how to do it.

I also know there is a need to deal with the following:

• Run macro as long as n < lastrow
• Change column when i = 1048000 then give i the value of i = 1

Any help out there?

 

[Fluff]

How about

Sub lricher()
   Dim i As Long, r As Long, c As Long
   Dim Cl As Range
   Dim Sp As Variant
   
   c = 3
   For Each Cl In Range("B1", Range("B" & Rows.Count).End(xlUp))
      Sp = Split(Cl.Value, ";") 'Split based on ";"
      For i = 0 To UBound(Sp)
         If r = 1040001 Then
            r = 1
            c = c + 1
         Else
            r = r + 1
         End If
         Cells(r, c).Value = Sp(i) 'Writing data on 3rd column
      Next i
   Next Cl
End Sub

 

[lricher]

@Fluff
This worked wonders!!!
Thanks ever so much.
I guess this is when i should say or wonder... "why have i not seeked help sonner ?! "
Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback.