Add columns to excel

Question

I am trying to create an excel macro that automatically inserts two columns in front of column D ... This procedure worked fine when I created it, here it is:

Sub AddColumns()
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

But when I open the worksheet and check it, all my data is shifted to the right for about 11 columns and 11 empty columns are added. I am sure this is due to some rows in which 11 columns are combined. Select statements select the first 11 columns of AK.

How to fix it?

Answer 1

This is due to the string Columns("D:D").Select. If you do not select this column code, it will work fine.

Use this instead:

With Range("D:D")
    .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With

, Select/Active:)

Answer 2

select

Sub AddColumns()
    Columns("D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Answer 3

If you select a column containing merged cells, the selection will be expanded for all columns that intersect these merged cells. In your case, 11 columns are selected.

In addition, Selection.Insert inserts as many columns as it selects: Voila.

Therefore, make sure that you do not merge the cells in your selection.