2 thoughts on “Using Excel Range Names to parse a Matrix into a List

  1. I’d prefer a tiny macro in this case:

    Sub M_snb()
    sn = Sheets("forex 2D Table").Cells(1).CurrentRegion

    ReDim sp((UBound(sn) - 1) * (UBound(sn, 2) - 1), 2)

    sp(0, 0) = "CCYUS"
    sp(0, 1) = "Date"
    sp(0, 2) = "Value"

    For j = 1 To UBound(sp)
    y = (j - 1) \ (UBound(sn, 2) - 1) + 2
    x = (j - 1) Mod (UBound(sn, 2) - 1) + 2
    sp(j, 0) = sn(1, x)
    sp(j, 1) = sn(y, 1)
    sp(j, 2) = sn(y, x)
    Next

    With Sheets("forex ccyus_date_value").Cells(1, 10)
    .Resize(UBound(sp) + 1, Ubound(sp, 2) + 1) = sp
    .CurrentRegion.Sort .Offset, , , , , , , True
    End With
    End Sub

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s