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
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
alfred-excel-vachris 
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