PDA

View Full Version : rearranging columns



lhardee
12-29-2008, 10:11 AM
Hello,

I am trying to write a macro that will rearrange columns in a certain order. Each column's 1st cell has a name as follows. Column A = Eqp Additional Info Column B = Equipment Name Column C = Time Up Column D = Alarm Type.

I would like the columns to be arranged as Column A = Alarm Type Column B = Time Up Column C = Equipment Name Column D = Eqp Additional Info.

I know I can just manually move columns...but each user's sheet could have thse columns in a different order as shown above. The only common point is that the column names are the same. I was trying to focus on that using maybe an array or some way to look for those column names and then rearrange.


Thanks

lhardee

lucas
12-29-2008, 11:05 AM
This might get you started in the right direction. If you wanted to move columns d, e & f to position b you would use D:F,b in the messagebox.


If you want to move column d to position b you would use d,b in the messagebox. The comma seperator is the key.

could probably be expanded on if it seems to be useful.

Option Explicit
Sub MoveColumns()
Dim Cols As String, Source As String, Target As String
Cols = InputBox("Enter column letter(s) to move and target column" & vbCr _
& "eg 'E:F,B' or 'F,B'")
Source = Split(Cols, ",")(0)
On Error Resume Next
'Fix semi colons
Source = Replace(Source, ";", ":")
Target = Split(Cols, ",")(1)
Columns(Source).Cut
Columns(Target).Insert
End Sub

lhardee
12-29-2008, 11:49 AM
Thanks lucas,


Your code does the trick but I am looking for a more automated way where the user is not asked what columns to rearrange. I will work with your code to see what I can come up with.

Thanks for the assistance. :thumb

lhardee

lucas
12-29-2008, 12:16 PM
If you need more help, post back here. This thread exemplifies the value of interoffice standards by the way.......

mikerickson
12-29-2008, 04:17 PM
Would Excel's native Sort (Option: left to right) on a custom list work for you?