- Option Explicit
- Sub ta()
- Dim arr, brr
- Dim sh1 As Worksheet, sh2 As Worksheet
- Dim i As Long, j As Long, k As Long
- Set sh1 = Worksheets("原始數據")
- Set sh2 = Worksheets("生成")
- sh1.Select
- arr = Range("b2:d" & Cells(Rows.Count, 1).End(xlUp).Row)
- ReDim brr(1 To UBound(arr, 1) * 3 + 1, 1 To 2)
- k = 1
- For i = 1 To UBound(arr)
- For j = 1 To 3
- If arr(i, j) <> "" Then
- brr(k, 1) = arr(i, j)
- sh2.Cells(k + 1, 1) = sh1.Cells(i + 1, 1)
- k = k + 1
- End If
- Next j
- Next i
- With sh2
- On Error Resume Next
- .Range("a1:b1") = sh1.Range("a1:b1").Value
- .[b2].Resize(UBound(brr, 1), UBound(brr, 2)) = brr
- For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row - 1
- If .Cells(i + 1, 1) = .Cells(i, 1) Then
- .Range(Cells(i, 1), Cells(i + 1, 1)).Merge
- End If
- Next i
- End With
- Set sh1 = Nothing
- Set sh2 = Nothing
- End Sub
复制代码 |