CODEWITHSUNDEEP
vbasplitinsertrow
Seraph
Seraph

Reputation: 1

Insert rows and split string delimited with semicolons

My values are in column H and the range of the entire table is A:N. I got the below table from the net and modified this to meet my requirement. However, it seems that I am missing out on something - would be really helpful if someone could help me figure that out. Here is the code that I used and it does nothing to my table. As I am new to VBA, a descriptive commentary would be really helpful to understand the code.

Sub splitByColH()
    Dim r As Range, i As Long, ar As Variant
    Set r = Worksheets("Sheet2").Range("H999999").End(xlUp)
    Do While r.Row > 1
        ar = Split(r.Value, ";")
        If UBound(ar) >= 0 Then r.Value = ar(0)
        For i = UBound(ar) To 1 Step -1
            r.EntireRow.Copy
            r.Offset(1).EntireRow.Insert
            r.Offset(1).Value = ar(i)
        Next
        Set r = r.Offset(-1)
    Loop
End Sub

Upvotes: 0

Views: 61

Answers (1)

Sam
Sam

Reputation: 5731

Sub splitByColH()
    Dim r As Range, i As Long, ar As Variant
    Set r = Worksheets("Sheet2").Range("H999999").End(xlUp) ' Set r to be the last cell used in col H

    Do While r.Row > 1 ' As long as the r row is > 1

        ar = Split(r.Value, ";") ' Take r cell (last in H col) and create an array of its values when split by ;
        If UBound(ar) >= 0 Then r.Value = ar(0) ' As long as ar contains more than one element
        For i = UBound(ar) To 1 Step -1 ' Step through all elements from last to first
            r.EntireRow.Copy ' Copy the entire row where r is
            r.Offset(1).EntireRow.Insert ' Paste it (insert) one row below
            r.Offset(1).Value = ar(i) ' Set the H col of the new row to be the i'th element of ar
        Next
        Set r = r.Offset(-1) ' point r to where it is minus one row
    Loop
End Sub

I have explained what your code does right now. It makes little sense, so I suppose you have a logic error somewhere. If you explain the wished result in more detail, it will be possible to give you a better answer..

Upvotes: 1

Related Questions