'Option Base 1
Option Explicit
'Option Private Module
'==================================================================================================
Sub Splitter()
Dim arr, aOut()
Dim DS$, t!, r&, rF&, rMax&, cc&, n&
t = Timer
r = Cells(Rows.Count, 1).End(xlUp).Row
arr = Cells(1, 1).Resize(r, 1).Value
ReDim aOut(1 To UBound(arr, 1), 1 To UBound(arr, 1))
aOut(1, 1) = arr(1, 1): rF = 1
DS = Application.International(xlDateSeparator)
For r = 2 To UBound(arr, 1)
If IsError(arr(r, 1)) Then GoTo nx Else If Len(arr(r, 1)) = 0 Then GoTo nx
If IsDate(Replace$(arr(r, 1), ".", DS)) Then
cc = cc + 1: n = FillNew(arr, rF, r - 1, aOut, cc)
rF = r: If rMax < n Then rMax = n
End If
nx:
Next r
If cc = 0 Then MsgBox "Only ONE block!", vbExclamation, "NOTHING": Exit Sub
cc = cc + 1: n = FillNew(arr, rF, UBound(arr, 1), aOut, cc): If rMax < n Then rMax = n
With ActiveSheet.UsedRange
[c1].Resize(.Rows.Count, .Columns.Count).ClearContents
End With
[c1].Resize(rMax, cc).Value = aOut
MsgBox "DONE", vbInformation, Format$(Timer - t, "0.00 sec")
End Sub
'--------------------------------------------------------------------------------------------------
Private Function FillNew(aOld, rF&, rL&, aNew(), cNew&) As Long
Dim r&, rr&
For r = rF To rL
If IsError(aOld(r, 1)) Then GoTo nx Else If Len(aOld(r, 1)) = 0 Then GoTo nx
rr = rr + 1: aNew(rr, cNew) = aOld(r, 1)
nx:
Next r
FillNew = rr
End Function
'================================================================================================== |