如何使用 VBA 颠倒选定范围的 Excel 列的顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/524844/
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
How to Reverse the Order of a Selected Range of Excel Columns using VBA
提问by imfrancisd
I am new to VBA and am trying to figure out how to reverse the order of a selected range of columns without hard coding. Every example I look out either hard codes the columns to reverse the order of, or assumes you want to reverse all the columns in the worksheet. I'm not trying to ask someone to write this for me, but as simple as this should be, as soon as someone explains what I am missing, I should be OK.
我是 VBA 的新手，正在尝试弄清楚如何在没有硬编码的情况下反转选定范围列的顺序。我查看的每个示例要么对列进行硬编码以反转其顺序，要么假设您要反转工作表中的所有列。我不是想请人为我写这个，但应该很简单，只要有人解释了我遗漏了什么，我应该就可以了。
Does anyone have any advice?
有人有建议吗？
回答by imfrancisd
You can use sortto do this.
您可以使用sort来执行此操作。
Using this method, you can arrange the columns any way you want.
使用此方法，您可以按您想要的任何方式排列列。
If you have data that looks like this:
如果您有如下所示的数据：
A B C
1 header1 header2 header3
2 dataA dataE dataI
3 dataB dataF dataJ
4 dataC dataG dataK
5 dataD dataH dataL
Add some numbers at the first row:
在第一行添加一些数字：
A B C
1 1 2 3
2 header1 header2 header3
3 dataA dataE dataI
4 dataB dataF dataJ
5 dataC dataG dataK
6 dataD dataH dataL
Then use sort, with no header rows, and then choose the option to sort left to rightinstead of top to bottom.
然后使用排序，没有标题行，然后选择从左到右而不是从上到下排序的选项。
Sorting by row 1, descending:
按第 1 行降序排序：
A B C
1 3 2 1
2 header3 header2 header1
3 dataI dataE dataA
4 dataJ dataF dataB
5 dataK dataG dataC
6 dataL dataH dataD
If you want to script this, use the option to sort by rowsinstead of by columns.
如果要编写此脚本，请使用按行而不是按列排序的选项。
I wrote a .vbs that does this here:
我在这里写了一个.vbs：
http://gallery.technet.microsoft.com/ScriptCenter/en-us/f6085342-a1ae-49d8-acfc-38368256ee42?lc=1033
http://gallery.technet.microsoft.com/ScriptCenter/en-us/f6085342-a1ae-49d8-acfc-38368256ee42?lc=1033
回答by imfrancisd
this code flips a whole range horizontally, one row at a time is a pain. enjoy
这段代码水平翻转整个范围，一次一行很痛苦。请享用
Sub FlipHorizontal()
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Set Rng = Selection
rw = Selection.Rows.Count
cl = Selection.Columns.Count
If Rng.Cells.Count = ActiveCell.EntireRow.Cells.Count Then
MsgBox "You May Not Select An Entire Row", vbExclamation, _
"Flip Selection"
Exit Sub
End If
If Rng.Cells.Count = ActiveCell.EntireColumn.Cells.Count Then
MsgBox "You May Not Select An Entire Column", vbExclamation, _
"Flip Selection"
Exit Sub
End If
ReDim Arr(rw, cl)
For cc = 1 To cl ' = Rng.Columns.Count
For rr = 1 To rw 'rr = Rng.Rows.Count
Arr(rr, cc) = Rng.Cells(rr, cc) '.Formula
a = Arr(rr, cc)
Next
Next
'copy arry to range flippingnhorizontal
cc = cl
For a = 1 To cl ' to loop the columns
For rr = 1 To rw 'rr = Rng.Rows.Count
Rng.Cells(rr, cc) = Arr(rr, a) '= .Formula
Next
cc = cc - 1
Next
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
回答by Nate Fowkes
I believe what you want to do looks something like this:
我相信你想做的事情是这样的：
A1 B1 C1 D1 E1 | E1 D1 C1 B1 A1
A1 B1 C1 D1 E1 | E1 D1 C1 B1 A1
A2 B2 C2 D2 E2 | E2 D2 C2 B2 A2
A2 B2 C2 D2 E2 | E2 D2 C2 B2 A2
If that's what you're trying to do, try this worksheet function:
如果这就是你想要做的，试试这个工作表功能：
=INDEX($A$1:$B$5,ROW(A1),6-COLUMN(A1))
=INDEX($A$1:$B$5,ROW(A1),6-COLUMN(A1))
Where 6 is 1 + the number of columns in the range you want to reverse. There's probably a better way to do that part, but I was going for simple here.
其中 6 是 1 + 要反转的范围内的列数。可能有更好的方法来做那部分，但我在这里想要简单。
Put that function in cell F1 and copy/paste it to the same dimensions as the original range. Then you can just cut/paste values.
将该函数放在单元格 F1 中并将其复制/粘贴到与原始范围相同的尺寸。然后你可以剪切/粘贴值。
回答by Mitch Wheat
From here: Flipping Or Mirroring A Range:
从这里：翻转或镜像范围：
This macro will reverse the order of a range of data. You may flip data in a single row or in a single column of data (i.e., an N by 1 array or an 1 by N array). You may not select and entire row or an entire column.
此宏将反转一系列数据的顺序。您可以翻转单行或单列数据中的数据（即 N x 1 数组或 1 x N 数组）。您不能选择整行或整列。
Public Sub FlipSelection()
Dim Arr() As Variant
Dim Rng As Range
Dim C As Range
Dim Rw As Long
Dim Cl As Long
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Set Rng = Selection
Rw = Selection.Rows.Count
Cl = Selection.Columns.Count
If Rw > 1 And Cl > 1 Then
MsgBox "Selection May Include Only 1 Row or 1 Column", _
vbExclamation, "Flip Selection"
Exit Sub
End If
If Rng.Cells.Count = ActiveCell.EntireRow.Cells.Count Then
MsgBox "You May Not Select An Entire Row", vbExclamation, _
"Flip Selection"
Exit Sub
End If
If Rng.Cells.Count = ActiveCell.EntireColumn.Cells.Count Then
MsgBox "You May Not Select An Entire Column", vbExclamation, _
"Flip Selection"
Exit Sub
End If
If Rw > 1 Then
ReDim Arr(Rw)
Else
ReDim Arr(Cl)
End If
Rw = 0
For Each C In Rng
Arr(Rw) = C.Formula
Rw = Rw + 1
Next C
Rw = Rw - 1
For Each C In Rng
C.Formula = Arr(Rw)
Rw = Rw - 1
Next C
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
回答by shahkalpesh
Selection.Columns(Selection.Columns.Count).Column- gives you the ending column numberSelection.Columns(1).Column- gives you the starting column number
Selection.Columns(Selection.Columns.Count).Column- 为您提供结束列号Selection.Columns(1).Column- 为您提供起始列号
You can do a reverse for loop using the above.
您可以使用上述方法进行反向循环。
回答by jheriko
Record a macro with relative cell references then examine the code. I do this whenever I am feeling too lazy to look up some functionality (because, lets face it, the documentation from MS is getting worse and worse for VBA).
记录具有相对单元格引用的宏，然后检查代码。每当我懒得查找某些功能时，我就会这样做（因为，让我们面对现实，MS 的文档对于 VBA 来说越来越糟糕）。
