- Option Explicit
- Sub 数据整理()
- Dim arr, iRow&, i&, j As Byte
- Dim Record&, Temp, Tempstr$
- Dim arrResult()
- Dim reg As Object
- Dim t#
- t = Timer
- iRow = Cells(Rows.Count, 1).End(xlUp).Row
- arr = Range("a1:c" & iRow)
- Record = 1
- Application.ScreenUpdating = False
- ReDim arrResult(1 To 3, 1 To 1)
- For i = 1 To 3
- arrResult(i, 1) = arr(1, i)
- Next
- Set reg = CreateObject("VBScript.regExp")
- With reg
- .Global = True
- .Pattern = "\,?\d+?\.?"
- For i = LBound(arr) + 1 To UBound(arr)
- Tempstr = StrConv(Replace(arr(i, 3), "1.", ""), vbNarrow)
- Tempstr = Replace(Tempstr, " ", "")
- Tempstr = Replace(Tempstr, vbLf, "")
- Tempstr = Replace(Tempstr, ";", ",")
- Tempstr = Replace(Tempstr, "?", ",")
-
- If .test(Tempstr) Then
- Temp = Split(.Replace(Tempstr, ","), ",")
- For j = LBound(Temp) To UBound(Temp)
- Record = Record + 1
- ReDim Preserve arrResult(1 To 3, 1 To Record)
- arrResult(1, Record) = arr(i, 1)
- arrResult(2, Record) = arr(i, 2)
- arrResult(3, Record) = Temp(j)
- Next
- Else
- If Len(Tempstr) = 0 Then
- Record = Record + 1
- ReDim Preserve arrResult(1 To 3, 1 To Record)
- arrResult(1, Record) = arr(i, 1)
- arrResult(2, Record) = arr(i, 2)
- arrResult(3, Record) = arr(i, 3)
- Else
- Temp = Split(Tempstr, ",")
- For j = LBound(Temp) To UBound(Temp)
- Record = Record + 1
- ReDim Preserve arrResult(1 To 3, 1 To Record)
- arrResult(1, Record) = arr(i, 1)
- arrResult(2, Record) = arr(i, 2)
- arrResult(3, Record) = Temp(j)
- Next
- End If
- End If
- Next
- End With
- Worksheets.Add
- Range("a1").Resize(Record, 3) = WorksheetFunction.Transpose(arrResult)
- With Columns("a:c")
- .HorizontalAlignment = xlLeft
- .AutoFit
- End With
- Application.ScreenUpdating = True
- t = Timer - t
- MsgBox "转化完成, 用时一共 " & t & "秒"
- End Sub
复制代码 能做出这个表的,绝对是人才。 |