CODEWITHSUNDEEP
vbaexcel
vivi xu
vivi xu

Reputation: 147

Unable to split cell content by line breaks

My VBA script is supposed to split content in one cell by line breaks into several rows, it works for some cells, date in one cell look like this:

a01gestmstrs2a 10.67.15.17 
a01gestmdb2a   10.67.15.19
a01gstdbldnim1a
a01rstdbldnim1a
a01gestmstrs2b (10.67.15.46)
a01restmdb2a (10.67.15.48)
a01gestmstrs2z 10.67.15.20 
a01gestmdb2b (10.67.15.47)
a01restmstrs2a (10.67.15.49)

However, it fails to split for some such as the sample provided above, I can't figure out why. My code:

Sub SplitMultipleHostnames()
Dim tmpArr As Variant
Dim s As String

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In Range("D2", Range("D3").End(xlDown))
For Each c In ActiveSheet.UsedRange
    s = c.Value
    If Trim(Application.Clean(s)) <> s Then
        s = Trim(Application.Clean(s))
        c.Value = s
    End If


If cell.Value <> "" Then
    If InStr(1, cell, Chr(10)) <> 0 Then
        tmpArr = Split(cell, Chr(10))

        cell.EntireRow.Copy

        cell.Offset(1, 0).Resize(UBound(tmpArr), 1).EntireRow.Insert xlShiftDown



        cell.Resize(UBound(tmpArr) + 1, 1) = Application.Transpose(tmpArr)

    End If


Else
    cell.EntireRow.Delete
    cell.Row = cell.Row - 1

End If
Next
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False


End Sub

Upvotes: 1

Views: 157

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

They are not actually Char(10) they are spaces. I changed the code to " " and it worked fine

  If cell.Value <> "" Then
                If InStr(1, cell, " ") <> 0 Then
                    tmpArr = Split(cell, " ")

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

The loop that uses Trim() and Clean() will remove all ASCII 10's and 13's from the worksheet.

There will be nothing to Split().

Upvotes: 3

Related Questions