クロス表→テーブル変換

Option Explicit

Public Sub Main()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets(1)

    unpivot sh, "データ"

End Sub

Public Function unpivot(sh As Worksheet, areaName As String)

    Dim rg As Range
    Set rg = sh.Cells.Range(areaName)

    Dim row As Integer
    Dim col As Integer
    Dim val As String
    Dim line As String

    Dim cell As Range
    For Each cell In rg
        line = ""

        If cell.Value = "" Then
            GoTo lbl_skip
        End If

        For col = 1 To rg.Column - 1
            line = line & sh.Cells(cell.row, col).Value & ","
        Next col

        For row = 1 To rg.row - 1
            line = line & sh.Cells(row, cell.Column).Value & ","
        Next row

        line = line & cell.Value
        Debug.Print line

lbl_skip:

    Next cell

End Function