Solved: Move columns

[Derek McGill]

Hi all
How can I move with vba the column of 10 cells in table 1 to the positions in table 2?
Note the # positions will change, but there will only be 10 of them the "#" can be changed to any non mumber.
Table 1
a b c d e f g h i j
1 -- #
2 ------------#
3 ---- #
4 ------ #
5 -------------#
6 #
7- #
8----- #
9---------- #
10------- #

Table 2
a b c d e f g h i j
1 #
2 -#
3 --#
4 ---#
5 ----#
6 -----#
7 ------#
8 -------#
9 --------#
10 --------#

Derek McGill

[mbarron]

A non VBA solution would be to enter =MATCH("#",A1:J1) In K1 and copy it down, then sort A1:K10 on column L ascending.

[Derek McGill]

Sorry can not sort data in the other 90 cells, the vertical col must move.
Derek

[mbarron]

Please post a workbook showing a before and after of what you wish to achieve.

Is the non number going from its current location, and then the rest of the row shifts to fill in the vacated spot?

[mbarron]

Maybe this?
Sub LeftToRightSort()
Range("a11").FormulaR1C1 = "=MATCH(""ZZ"",R[-10]C:R[-1]C,1)"
Range("a11").AutoFill Destination:=Range("A11:J11"), Type:=xlFillDefault
Range("A1:J11").Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Range("A11:J11").ClearContents
End Sub

[Derek McGill]

No the 9 cells move with the "#"

[mbarron]

The macro I posted will do what you've shown in the workbook.

[Derek McGill]

Thanks that works well,
Can you add comments to help me follow how it works ?
Derek ( 04:00 I have to go to bed !!)

[mbarron]

It is an slightly adjusted recorded macro that uses Excel's Left to Right sort option.
Sub LeftToRightSort()
'enter Match formula in A11
Range("a11").FormulaR1C1 = "=MATCH(""ZZ"",R[-10]C:R[-1]C,1)"
'fill formula across to J column
Range("a11").AutoFill Destination:=Range("A11:J11"), Type:=xlFillDefault
'sorts left to right
Range("A1:J11").Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
'clears values in cells since they are not needed
Range("A11:J11").ClearContents
End Sub