Sub my_split()
Dim ar, br(), rr, j%, i&, s$, m%
ar = Me.Range("a1").CurrentRegion
ReDim br(1 To UBound(ar), 1 To 1)
For i = 1 To UBound(ar)
s = ar(i, 1)
If s <> "" Then
m = m + 1
rr = Split(RTrim(s), " ")
If UBound(rr) >= UBound(br, 2) Then ReDim Preserve br(1 To UBound(ar), 1 To UBound(rr) + 1)
For j = 0 To UBound(rr)
br(m, j + 1) = rr(j)
Next
End If
Next
If m > 0 Then
Sheet1.UsedRange.Offset(, 1).Clear
Sheet1.[b1].Resize(m, UBound(br, 2)) = br
MsgBox "春已走,花又落!" & Chr(10) & "我的痛怎么形容!", vbInformation, "爱相随"
End If
End Sub
|