Moving Rows Reversing Columns by VBA

[Beatrix]

Hi Everyone ,

I need to put the rows and columns in a specific order in multiple tables in excel. Is there any time saving way to do this with VBA?

What I need is put the Rows in order 5A, 5B, 5C, 4A, 4B, 4C, 3A, 3B, 3C, B or N

Also Columns need to be reversed as U, G, F, E, D, C, B, A, *

Column 2SK should stay as it is.

I attached before after scenarios.

PS: In original spreadsheet there are 2 tabs and each tab has multiple tables.

Cheers
B.



Sub Level
2SK
*
A
B
C
D
E
F
U


3A
9



3
3
1




3B
9




1





3C
8










4A
4
1
4
14
13
6





4B
9

2
10
22
10
2




4C
8


2
14
7
3

1


5A



1







5B

2
4
1







5C
1
1
16
20
4
2





B or N
15


2
3
1

1

[SamT]

Rows:

For i = 0 to 7
Rows(11).Cut
Rows(i+3).Insert
Next

Use the same technique for the columns

If the tables do not align vertically, you will need to use arrays of ranges.

[nilem]

Hi Beatrix,
as an option

Sub ertert()
Dim x, y, i&, j&, k&, RowsArr, ClmnsArr
RowsArr = Array("Sub Level", "5A", "5B", "5C", "4A", "4B", "4C", "3A", "3B", "3C", "B or N")
ClmnsArr = Array("Sub Level", "2SK", "U", "F", "E", "D", "C", "B", "A", "*")

x = Sheets("before").Range("B2").CurrentRegion.Value
ReDim y(1 To UBound(RowsArr) + 1, 1 To UBound(ClmnsArr) + 1)

With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = LBound(RowsArr) To UBound(RowsArr)
j = j + 1: .Item(RowsArr(i)) = j
Next i
For i = LBound(ClmnsArr) To UBound(ClmnsArr)
k = k + 1: .Item(ClmnsArr(i)) = k
Next i
For i = 1 To UBound(x)
For j = 1 To UBound(x, 2)
y(.Item(x(i, 1)), .Item(x(1, j))) = x(i, j)
Next j
Next i
End With
Sheets("after").Range("B16").Resize(UBound(x), UBound(x, 2)).Value = y
End Sub

[mancubus]

excellent nilem :clap:

[Beatrix]

Nilemmmm that's brilliant thanks very much:bow:

Much appreciated.
B.