Categories xlGenie Using Excel Range Names to parse a Matrix into a List Post author By excel1star Post date December 11, 2014 2 Comments on 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 Share this:LinkedInLike this:Like Loading... Related Tags Excel VBA Automation By excel1star Senior Excel VBA Automation Developer. Ex Aerospace Engineer with Grumman Aerospace View Archive → ← Enhanced Insertion Sorting → A User Controlled Demonstration of the Aitken-Lagrange Construction for Points on a Parabola. 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 LikeLike Thanks for sharing. You present a number of interesting vba coding techniques. Regards Alfred LikeLike Leave a Reply Cancel reply Enter your comment here... Fill in your details below or click an icon to log in: Email (required) (Address never made public) Name (required) Website You are commenting using your WordPress.com account. ( Log Out / Change ) You are commenting using your Google account. ( Log Out / Change ) You are commenting using your Twitter account. ( Log Out / Change ) You are commenting using your Facebook account. ( Log Out / Change ) Cancel Connecting to %s Notify me of new comments via email. Notify me of new posts via email. This site uses Akismet to reduce spam. Learn how your comment data is processed.

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 LikeLike

Thanks for sharing. You present a number of interesting vba coding techniques. Regards Alfred LikeLike

## 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

LikeLike

Thanks for sharing. You present a number of interesting vba coding techniques.

Regards

Alfred

LikeLike