Hi jasonleewkd,
try
Sub ertert()
Dim x, y(), i&, j&, k&
x = Sheets("Unpivotable table").Range("B1").CurrentRegion.Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To 6)
For j = 2 To UBound(x, 2)
For i = 5 To UBound(x, 1)
If Len(x(i, j)) Then
k = k + 1
y(k, 1) = x(i, 1)
y(k, 2) = x(1, j)
y(k, 3) = x(2, j)
y(k, 4) = x(3, j)
y(k, 5) = x(4, j)
y(k, 6) = x(i, j)
End If
Next i
Next j
With Sheets.Add.Range("A1")
.Resize(, 6).Value = Array("Period", "Returns type", _
"Country", "Sector", "Bloomberg ticker", "Value")
.Offset(1).Resize(k, 6).Value = y()
End With
End Sub
Bookmarks