add columns in excel

Question

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?

Answer 1

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:)