Solved: Excel- align rows by column. Works but too slow!

[tx7399]

9589

Hi everyone,
I need to align data based on the values in three different columns.
Change this:
A B C D E F G
1 1 a 1 a 2 i
2 2 a 2 i 4 a
3 4 a 4 a 6 a
4 5 i
5
6
To this;
A B C D E F G
1 1 a 1 a
2 2 a 2 i 2 i
3
4 4 a 4 a 4 a
5 5 i
6 6 a

Note: B&C, D&E, and F&G move together
Cols B, D, and E are sorted ascending
Col A is just a series 1 - 25,000

I have attached test data with a macro that works- but is too slow!

Any suggestions are appreciated.

[p45cal]

I got a 6 fold increase in speed by just adding
Application.ScreenUpdating=False
at the beginning of your sub and
Application.ScreenUpdating=True
at the end.

I got an 18 fold increase in speed with the following (which might not work if you data doesn't follow certain requirements!):Sub blah()
Application.ScreenUpdating = False
Set xxx = Range(Range("A2"), Range("A2").End(xlDown))
For Each colm In Array("B", "D", "F")
For Each cll In xxx.Cells
rw = cll.Row
A = cll.Value
B = Range(colm & rw).Value
If A <> B And Not IsEmpty(B) Then
Range(colm & rw).Resize(B - A, 2).Insert Shift:=xlDown
End If
Next cll
Next colm
Application.ScreenUpdating = True
End Sub

[tx7399]

Thanks p45cal,

It runs great! Fast! Using arrays made a huge difference.

:bow: Thanks!