How to split and restructure cells using excel VBA

Question

The code I currently use splits:

Original Data

And changes it to:

Modified Data

However, this is the format in which I require the data to be in:

Required Format

This is a copy of my current code:

Sub SplitCells()
Dim rColumn As Range
Dim lFirstRow As Long
Dim lLastRow As Long
Dim lRow As Long
Dim lLFs As Long

Set rColumn = Columns("D")
lFirstRow = 1
lLastRow = rColumn.Cells(Rows.Count).End(xlUp).Row

For lRow = lLastRow To lFirstRow Step -1
    lLFs = Len(rColumn.Cells(lRow)) - Len(Replace(rColumn.Cells(lRow), vbLf, ""))
    If lLFs > 0 Then
        rColumn.Cells(lRow + 1).Resize(lLFs).EntireRow.Insert xlShiftDown
        rColumn.Cells(lRow).Resize(lLFs + 1).Value = Application.Transpose(Split(rColumn.Cells(lRow), vbLf))
    End If
Next lRow
End Sub

Any help/comments will be appreciated.

score 3 · Accepted Answer · 2013-12-13 11:50:21Z

3

call ResizeToFit macro at the end of your code

Add ResizeToFit right before End Sub in your current code

ie.

...
Next lRow
ResizeToFit ' or Call ResizeToFit
End Sub
...

add this code to the same module as a new sub

Sub ResizeToFit()
Application.ScreenUpdating = False

    Dim i As Long
    For i = Range("D" & Rows.Count).End(xlUp).Row To 1 Step -1
        If IsEmpty(Range("D" & i)) Then
            Rows(i & ":" & i).Delete
        Else
            Range("E" & i) = Split(Range("D" & i), Chr(32))(1)
            Range("D" & i) = Split(Range("D" & i), Chr(32))(0)
        End If
    Next i

    For i = 1 To 5
        If i <> 4 Then
            Cells(1, i).Resize(Range("D" & Rows.Count).End(xlUp).Row, 1).Value = Cells(1, i)
        End If
    Next

Application.ScreenUpdating = True
End Sub

Taking THIS

enter image description here

and running my code produces

enter image description here

edited Dec 13, 2013 at 11:50

answered Dec 13, 2013 at 11:17

user2140173

Sign up to request clarification or add additional context in comments.

3 Comments

user2140173 Over a year ago

@Chris I have edited my answer to demonstrate that it does what you want it to do....You must be doing something wrong or not supplying all the required details

user2140173 Over a year ago

Make sure the visible empty cells in column D are actually EMPTY for the IsEmpty(cell) to work properly.. try changing the condition from IsEmpty(Range("D" & i)) to Len(Range("D" & i)) = 0

Chris Over a year ago

Pefect, you got it! there was a space that was causing the problem!

brWHigino · Accepted Answer · 2013-12-13 11:20:52Z

Sub SplitCells()
    Dim rColumn As Range
    Dim lFirstRow As Long
    Dim lLastRow As Long
    Dim lRow As Long
    Dim lLFs As Long

    Set rColumn = Columns("D")
    lFirstRow = 1
    lLastRow = rColumn.Cells(Rows.Count).End(xlUp).Row

    For lRow = lLastRow To lFirstRow Step -1
        lLFs = Len(rColumn.Cells(lRow)) - Len(Replace(rColumn.Cells(lRow), vbLf, ""))
        If lLFs > 0 Then
            rColumn.Cells(lRow + 1).Resize(lLFs).EntireRow.Insert xlShiftDown
            rColumn.Cells(lRow).Resize(lLFs + 1).Value = Application.Transpose(Split(rColumn.Cells(lRow), vbLf))
        End If
        Dim curRow As Integer
        curRow = lRow + lLFs
        While curRow >= lRow
            If Application.CountA(Rows(curRow).EntireRow) = 0 Then
                Rows(curRow).Delete
            Else
                rColumn.Cells(curRow).Offset(0, 1).Value = Split(rColumn.Cells(curRow), " ")(1)
                rColumn.Cells(curRow).Value = Split(rColumn.Cells(curRow), " ")(0)
                rColumn.Cells(curRow).Offset(0, -3).Value = rColumn.Cells(lRow).Offset(0, -3).Value
                rColumn.Cells(curRow).Offset(0, -2).Value = rColumn.Cells(lRow).Offset(0, -2).Value
                rColumn.Cells(curRow).Offset(0, -1).Value = rColumn.Cells(lRow).Offset(0, -1).Value
            End If
            curRow = curRow - 1
        Wend
    Next lRow
End Sub

Inch High · Accepted Answer · 2013-12-13 12:33:31Z

This is just from a recorded macro so it needs cleaning up.

ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-5)"
    Range("E1:E4").Select
    Selection.FillDown
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],4)"
    Range("F1:F4").Select
    Selection.FillDown
    Range("E1:F4").Select
    Selection.Copy
    Range("E1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

You may not need the cut, paste and column delete if you're happy with Column D staying as it is and having the split parts to the right. In which case

ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-5)"
    Range("E1:E4").Select
    Selection.FillDown
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],4)"
    Range("F1:F4").Select
    Selection.FillDown

Sorry - ActiveCell is E1.

Collectives™ on Stack Overflow

How to split and restructure cells using excel VBA

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related