vba - 无法按换行符拆分单元格内容

Question

我的 VBA 脚本应该将一个单元格中的内容逐行拆分为几行，它适用于某些单元格，一个单元格中的日期如下所示:

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)

但是，对于上面提供的示例，它无法拆分，我不知道为什么。
我的代码:

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

Answer 1

使用 的循环修剪() 和 清洁()将从工作表中删除所有 ASCII 10 和 13。

将一无所有拆分() .