使用 Excel VBA 向右移动列数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9194277/
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
Column data moving to right with Excel VBA
提问by user569125
Below is my excl data:
以下是我的 excl 数据：
A B c ... F G
1 test vb1 testing1 open
2 test1 vb2 testing1 close
2 test2 vb3 testing1
4 test3 vb4 testing1
I want to move F column data to B column and shift B column data to right; so before B will be C.
我想将 F 列数据移到 B 列并将 B 列数据向右移动；所以在 B 之前是 C。
How can I do with this using Excel VBA programming?
我该如何使用 Excel VBA 编程来解决这个问题？
回答by JMax
Try this:
尝试这个：
Option Explicit
Sub MoveColumns()
With ActiveSheet
.Columns("F:F").Cut
.Columns("B:B").Insert Shift:=xlToRight
.Columns("C:C").Cut
.Columns("E:E").Insert Shift:=xlToRight
End With
Application.CutCopyMode = False
End Sub
To use this:
要使用这个：
- open the Visual Basic Editor: Tools > Macro > Visual Basic Editor
- insert a module (right-click on VBAProject and Insert > Module)
- paste the above code in this new module.
- 打开 Visual Basic 编辑器：工具 > 宏 > Visual Basic 编辑器
- 插入模块（右键单击 VBAProject 并插入 > 模块）
- 将上面的代码粘贴到这个新模块中。
You can then execute the code from Excel: Tools > Macro... > Macros...
然后您可以从 Excel 执行代码：工具 > 宏... > 宏...
[EDIT] Another try without copy-pasting
[编辑] 另一个没有复制粘贴的尝试
Option Explicit
Sub copyWithArray()
Dim lLastrow As Long
Dim aValues As Variant
With ActiveSheet
lLastrow = .Cells(.Rows.Count, "AE").Row
'store data from the column F
aValues = .Range("F1:F" & lLastrow).Value
'"move" data a column further
.Range("C1:AE" & lLastrow).Value = .Range("B1:AD" & lLastrow).Value
'copy data from column C to column B
.Range("B1:B" & lLastrow).Value = .Range("C1:C" & lLastrow).Value
'restore data copied from column F to column B
.Range("B1:B" & lLastrow).Value = aValues
End With
End Sub
回答by Siddharth Rout
so Before B will be C.
所以在 B 之前将是 C。
I had to re read it many times to actually understand what you are trying to say. And I might not have still got it correct. :) Are you saying this? Please confirm.
我不得不重读很多遍才能真正理解你想说什么。我可能还没有得到正确的答案。:) 你是说这个吗？请确认。
"So B will now be in place of C as C moves 1 place towards right when you insert F in place of B"
“因此，当您插入 F 代替 B 时，B 现在将代替 C，因为 C 向右移动 1 个位置”
If yes, then all you need is just the first two lines from Jmax's code
如果是，那么您只需要 Jmax 代码的前两行
Sub Sample()
Columns("F:F").Cut
Columns("B:B").Insert Shift:=xlToRight
End Sub
