|
'-------------------------------------------
Sub transform_m()
'-------------------------------------------
' 시트
Dim sht As Worksheet: Set sht = Worksheets("result")
' 데이타 range
Dim rngX As Range
Set rngX = sht.Range("A1").CurrentRegion
Set rngX = rngX.Offset(1, 0).Resize(rngX.Rows.Count - 1)
Dim v As Variant
' 데이타 담을 배열
Dim arr As Variant: arr = Array()
Dim rRow As Range, iCnt As Long
' line no-2, sep no - 3
' 각 행을 돌며
For Each rRow In rngX.Rows
' Line No
iCnt = rRow.Cells(2).Value
'2차원 배열, 행-1, 열-11
v = rRow.Value
' line no 만큼 행 배열 생성
For r = 1 To iCnt
'1, 2, 3. ..
v(1, 3) = r ' sep no
' 첫번째 행 외엔 line 셀 빈칸
If r > 1 Then v(1, 1) = ""
' 배열에 추가
arr = push(arr, v)
Next
Next rRow
'-----------------------
' 2차원 배열로 변환
'-----------------------
arr = WorksheetFunction.Transpose(WorksheetFunction.Transpose(arr))
' 시트에 넣기
sht.Range("M2").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub
' 배열에 요소 추가
'--------------------------------------------------------
Function push(col As Variant, ele As Variant) As Variant
'--------------------------------------------------------
Dim i As Long: i = UBound(col, 1) + 1
ReDim Preserve col(i)
col(i) = ele
push = col
End Function
|
|