4

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:

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.

How do I fix this?

AAlferez
  • 1,480
  • 1
  • 22
  • 48
user1634700
  • 165
  • 3
  • 12

3 Answers3

5

It happens because of the line Columns("D:D").Select. If you don't select this columns code would work fine.

Use this one instead:

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

And, How to avoid using Select/Active statements:)

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
2

You don't use select to do this

Sub AddColumns()
    Columns("D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
Steven Martin
  • 3,150
  • 1
  • 20
  • 27
1

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.

Further, Selection.Insert inserts as many columns are select: Voila.

Therefore make sure you do not have merged cells in your selection.

Wolfgang Kuehn
  • 12,206
  • 2
  • 33
  • 46