Categories
xlGenie

Using Excel Range Names to parse a Matrix into a List

A set of Excel/VBA procedures that can be used to parse a Matrix into a List.Using Excel Range Names to Parse a Matrix

By excel1star

Senior Excel VBA Automation Developer.
Ex Aerospace Engineer with Grumman Aerospace

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

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 to snb Cancel 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.