vba 在excel中添加列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22259358/
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
add columns in excel
提问by user1634700
I am trying to create an excel macro that automatically inserts two columns before column D... The procedure worked fine when I created it, here it is:
我正在尝试创建一个 excel 宏，它会在 D 列之前自动插入两列......创建它时该过程运行良好，这里是：
Sub AddColumns()
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
But when I open my worksheet and try it out, all my data is pushed to the right about 11 columns and 11 blank columns are inserted. I'm sure this has to with some rows having 11 columns merged. The Select statements selects the first 11 columns A-K.
但是当我打开我的工作表并尝试它时，我的所有数据都被推到了右边，大约插入了 11 列和 11 个空白列。我确定这与合并了 11 列的某些行有关。Select 语句选择前 11 列 AK。
How do I fix this?
我该如何解决？
回答by Dmitry Pavliv
It happens because of the line Columns("D:D").Select. If you don't select this columns code would work fine.
它发生是因为线Columns("D:D").Select。如果您不选择此列代码将正常工作。
Use this one instead:
改用这个：
With Range("D:D")
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With
回答by Steven Martin
You don't use select to do this
您不使用 select 来执行此操作
Sub AddColumns()
Columns("D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
回答by Wolfgang Kuehn
If you select column which contains merged cells, the selection is expanded to all columns which intersect these merged cells. In your case 11 columns are selected.
如果您选择包含合并单元格的列，则选择将扩展到与这些合并单元格相交的所有列。在您的情况下，选择了 11 列。
Further, Selection.Insert inserts as many columns are select: Voila.
此外，Selection.Insert 插入尽可能多的列选择：瞧。
Therefore make sure you do not have merged cells in your selection.
因此，请确保您的选择中没有合并单元格。
