Loop through all rows with data not just first row

[1819]

This macro to split up strings is written to deal with the first row only.

Only column A has data, but the number of rows will vary. There are no gaps.

Please could you modify the code so that it loop through all rows. I have tried to do this and have not succeeded.

Thanks.



Sub Split()
Dim txt As String
Dim i As Integer
Dim FullName As Variant
txt = ActiveCell.Value
FullName = Split(txt, "ADD")
For i = 0 To UBound(FullName)
Cells(1, i + 1).Value = FullName(i)
Next i
End Sub

[Kenneth Hobs]

I am unclear what you want. This code iterates column A and if ADD is in it, it treats it as a delimiter. e.g.
A1=1ADD2. Then after a run, A1=1, B1=2.

I do recommend not using reserved command words as your names.

Sub sSplit()
Dim c As Range, i As Long, s() As String
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
s() = Split(c.Value, "ADD")
c.Resize(1, UBound(s) + 1).Value = s()
Next c
End Sub

[1819]

I am unclear what you want. This code iterates column A and if ADD is in it, it treats it as a delimiter. e.g.
A1=1ADD2. Then after a run, A1=1, B1=2.

I do recommend not using reserved command words as your names.

Sub sSplit()
Dim c As Range, i As Long, s() As String
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
s() = Split(c.Value, "ADD")
c.Resize(1, UBound(s) + 1).Value = s()
Next c
End Sub

This is perfect, thank you Kenneth.