Excel vba Tips 表形式のデータをリスト形式に変換する

利用想定ケース：既存の票をpivotのデータとして使いたいとき
利用方法：表を選択した状態でマクロ実行

before

	愛媛	和歌山
2012	100	110
2013	150	160

after

2012	愛媛	100
2012	愛媛	110
2013	和歌山	150
2013	和歌山	160

Option Explicit

Sub table2list()
    Dim c As Range
    Dim cols() As String
    Dim i As Integer
    Dim labelYColN As Integer
    Dim labelXRowN As Integer
    Dim pointer As Integer
    
    ReDim cols(Selection.Columns.Count)
    i = 1
    labelYColN = Selection(1).Column
    labelXRowN = Selection(1).Row
    pointer = Selection(Selection.Count).Row + 2
    
    Dim labelY As String
    For Each c In Selection
        If labelXRowN = c.Row Then
            cols(i) = c.Value
            i = i + 1
        ElseIf labelYColN = c.Column Then
            labelY = c.Value
        Else
            ActiveSheet.Cells(pointer, labelYColN).Value = labelY
            ActiveSheet.Cells(pointer, labelYColN + 1).Value = cols(c.Column)
            ActiveSheet.Cells(pointer, labelYColN + 2).Value = c.Value
            pointer = pointer + 1
        End If
    Next

End Sub