excel - 불규칙하고 일관되지 않은 데이터에서 행을 열로 변환

이 작업을 수행해야합니다. 데이터가 어디에 있고 붙여 넣을지를 가정하고 다음을 변경하는 것을 잊지 마십시오.

Option Explicit
Sub Transpose()
    Dim LastRow As Long 'last row on the sheet
    Dim TransposeRow As Long 'row where we transpose
    Dim x As Long 'columns
    Dim C As Range 'faster looping through cells with For Each C in range
    With ThisWorkbook.Sheets("MySheet") 'change this to your sheet
        'To assign the last row im gonna assume your data is in column A or 1(B would be 2 and so...)
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row 'Last row with data
        TransposeRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1 'on column B will be pasting the data
        x = 2 'initialize x being 2 as for B column
        For Each C In .Range("A2:A" & LastRow)
            If C = vbNullString Then 'in case the cell is blank we jump a row
                TransposeRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1 ' recalculate row for transposing data
                x = 2 'reinitialize column counter
            Else
                .Cells(TransposeRow, x) = C 'we copy the value to the row and column empty
                x = x + 1 'add 1 column
            End If
        Next C
    End With
End Sub

유용한 접근 방식을 보여주기 위해 코드를 편집했습니다. 하나의 셀 데이터에 대한 조건을 추가해야합니다.

Sub Transpose2()
' Transpose Macro
' Keyboard Shortcut: Ctrl+Shift+T
Do Until IsEmpty(ActiveCell.Value)
    If IsEmpty(ActiveCell.Offset(1, 0).Value) Then
        Selection.Copy
        ActiveCell.Offset(0, 1).Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        ActiveCell.Offset(0, -1).Range("A1").Select
    Else
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        ActiveCell.Offset(0, 1).Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        ActiveCell.Offset(0, -1).Range("A1").Select
        Selection.End(xlDown).Select
    End If
    Application.CutCopyMode = False
    Selection.End(xlDown).Select
 Loop
End Sub

참고 : select 사용  일반적으로 좋은 생각이 아닙니다. select 삭감의 예  다음과 같습니다.

Sub Transpose3()
' Transpose Macro
' Keyboard Shortcut: Ctrl+Shift+T
Do Until IsEmpty(ActiveCell.Value)
    If IsEmpty(ActiveCell.Offset(1, 0).Value) Then
        ActiveCell.Copy ActiveCell.Offset(0, 1)
    Else
        Range(ActiveCell, ActiveCell.End(xlDown)).Copy
        ActiveCell.Offset(0, 1).Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        ActiveCell.Offset(0, -1).Range("A1").End(xlDown).Select
    End If
    Application.CutCopyMode = False
    Selection.End(xlDown).Select
 Loop
End Sub