【エクセルマクロ】行列を入れ替える：3選(動画あり)

Sub 表の行列を入れ替える()

    Range("A1:E5").Copy
    Range("G1").PasteSpecial Paste:=xlPasteValues, Transpose:=True

End Sub

Sub 横リストを縦リストに変換()

Dim i As Long
Dim j As Long
Dim Z As Long
Dim YokoLoop As Long
Dim TateLoop As Long

    '横ループ回数（A~E列）
    YokoLoop = 5

    '縦ループ回数（1~6行目）
    TateLoop = 6

    '縦ループ
    For i = 1 To TateLoop

        Z = 1

        '横ループ
        For j = 1 To YokoLoop

            'データ転記
            Cells(Z, 6 + i) = Cells(i, j)
            Z = Z + 1

        Next j
    Next i

 End Sub

Sub 行列変換()

Dim i As Long
Dim j As Long
Dim Z As Long
Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim LastRow As Long
Dim LastClm As Long

    Set Sht1 = Sheets("Sheet1")
    Set Sht2 = Sheets("Sheet2")

    '最終行、最終列を取得
    LastRow = Sht1.Cells(Rows.Count, 1).End(xlUp).Row
    LastClm = Sht1.Range("A1").End(xlToRight).Column

    '横ループ
    For i = 1 To LastRow

        Z = 1

        '縦ループ
        For j = 1 To LastClm

            'データ転記
            Sht2.Cells(Z, i) = Sht1.Cells(i, j)
            Z = Z + 1

        Next j
    Next i

 End Sub