在 Excel VBA 中重新排序多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23702934/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reordering Multiple Columns in Excel VBA
提问by 114
Is there a way to create a 'permutation' macro where you input a set of columns (A....Z) and it gives a chosen alternate ordering (e.g. (B,A,E,D,C,...,Z))? I imagine this is something that has been done before but it is surprisingly hard to find any precedent.
有没有办法创建一个“排列”宏，您可以在其中输入一组列 (A....Z) 并给出一个选择的替代顺序（例如 (B,A,E,D,C,..., Z))？我想这是以前做过的事情，但很难找到任何先例。
Initially I was thinking of copying / pasting using Range().Copy/ .Pastein a tedious way or similarly with Columns, that is:
最初我正在考虑使用Range().Copy/.Paste以一种乏味的方式或类似的方式复制 / 粘贴Columns，即：
Columns("C:C").Insert Shift:=xlToRight
Columns("D:D").Cut
Columns("A:A").Insert Shift:=xlToRight
Columns("G:G").Cut
Columns("E:E").Insert Shift:=xlToRight
...
UPDATE:
更新：
I did find the following code here:
我确实在这里找到了以下代码：
Sub REORDER()
Dim arrColOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer
'Place the column headers in the end result order you want.
arrColOrder = Array("COLUMN 2", "COLUMN 4", "COLUMN 6", "COLUMN 10", "COLUMN 1", _
"COLUMN 9", "COLUMN 3", "COLUMN 8", "COLUMN 7", "COLUMN 5")
counter = 1
Application.ScreenUpdating = False
For ndx = LBound(arrColOrder) To UBound(arrColOrder)
Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
If Not Found Is Nothing Then
If Found.Column <> counter Then
Found.EntireColumn.Cut
Columns(counter).Insert Shift:=xlToRight
Application.CutCopyMode = False
End If
counter = counter + 1
End If
Next ndx
Application.ScreenUpdating = True
End Sub
What is the process for calling this code in a larger macro?
在较大的宏中调用此代码的过程是什么？
采纳答案by David Zemens
Something like this should get you started. It will need some revision that I don't have time for, if you intend to use it on tables that do not begin in Row 1.
像这样的事情应该让你开始。如果您打算在不是从第 1 行开始的表上使用它，它将需要一些我没有时间进行的修改。
Sub Reorder()
Dim dict As Object
Dim rng As Range, c As Integer
Dim colRng As Range
Set dict = CreateObject("Scripting.Dictionary")
Set rng = Application.InputBox("Select table range", "Select Table", Type:=8)
If rng Is Nothing Then Exit Sub
'you should input a comma-delimited list of column letters, e.g., "E,B,C,A,D"
newOrder = Application.InputBox("Specify new order", "New order")
If Not rng.Columns.Count - 1 = UBound(Split(newOrder, ",")) Then
MsgBox "Invalid selection", vbCritical
End If
For Each v In Split(newOrder, ",")
v = Trim(v)
Set colRng = Range(Columns(v).Address).Resize(rng.Rows.Count)
dict(colRng.Address) = colRng.Value
Next
For Each k In dict.Keys()
c = c + 1
rng.Columns(c).Value = dict(k)
Next
Set dict = Nothing
End Sub
