I have written some code that transforms 2 way matrices into 1 way matrices. Although it's comfortable to use, it's very sluggish and slow in execution. Does anyone have any idea how to improve the performance? I'm aware that the same transformation can be done with standard formulas, but solely for learning reasons I would like to stick with vba.
How to use it - select whole matrix and execute the Sub.
Sub TwoToOneMatrix() Set Mrange = Selection colsX = Mrange.Columns.Count rowsX = Mrange.Rows.Count Set objects = Range(Mrange.Columns(1).Rows(2), Mrange.Columns(1).Rows(rowsX)) Set titleX = Range(Mrange.Columns(1).Rows(1), Mrange.Columns(1).Rows(1)) Set Rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8) 'Column 1 Rng.Value = titleX.Value def = 1 For j = 2 To objects.Rows.Count + 1 curCountry = Mrange.Columns(1).Rows(j) For i = 1 To colsX - 1 Rng.Offset(def, 0).Value = curCountry def = def + 1 Next i Next j 'Column 2 def = 1 Rng.Offset(0, 1).Value = "Date" For j = 1 To objects.Rows.Count For i = 2 To colsX curDate = Mrange.Columns(i).Rows(1) Rng.Offset(def, 1).Value = curDate def = def + 1 Next i Next j 'Column 3 Rng.Offset(0, 2).Value = "Values" def = 1 For r = 2 To rowsX For c = 2 To colsX Rng.Offset(def, 2).Value = Mrange.Columns(c).Rows(r) def = def + 1 Next c Next r End Sub