Insert range multiple times in VBA

[YasserKhalil]

Hello everyone
I have a line of code

Code: Select all

sh.Range("D7:D" & lr).Insert Shift:=xlToRight

This will move the range to be E7:E & lr ..
How can I repeat and move the range multiple times so as to make the range reach column I?

I could do it using this line

Code: Select all

sh.Range("D7:D" & lr).Cut sh.Range("D7:D" & lr).Offset(, 5)

But I welcome any more ideas ..

[HansV]

Like this:

Code: Select all

    Dim rng As Range
    Set rng = sh.Range("D7:D" & lr)
    Do
        rng.Insert Shift:=xlToRight
    Loop Until rng.Column = 9

[YasserKhalil]

Thanks a lot, my tutor.
Best Regards

[Doc.AElstein]

Hello Yasser,
I am just a little curious, that's all…
Are you sure you want what Hans’s macro does?

If this is your before worksheet,
http://i.imgur.com/I4IjZz9.jpg

UnmovedD.JPG

Then, if you apply Hans macro in this form,

Code: Select all

 Sub Insertses() '  https://eileenslounge.com/viewtopic.php?p=280700#p280700
Dim Sh As Worksheet: Set Sh = ActiveSheet
Dim Lr As Long: Let Lr = 9
Dim rng As Range
 Set rng = Sh.Range("D7:D" & Lr)
    Do
     rng.Insert Shift:=xlToRight
    Loop Until rng.Column = 9
End Sub

Then this is your result:
http://i.imgur.com/VJY5DvW.jpg

MovedD.JPG

If that is what you want, then fair enough. No Problem. - It just seems a bit strange to me that you want to do that.
So I was just checking, that’s all

Alan

BTW, these macros will produce the same final results

Code: Select all

 Sub InsertOnce()
Dim Sh As Worksheet: Set Sh = ActiveSheet
Dim Lr As Long: Let Lr = 9
Dim rng As Range
 Set rng = Sh.Range("D7:H" & Lr)
 rng.Insert Shift:=xlToRight
End Sub

Code: Select all

Sub Insertseses2() '  https://eileenslounge.com/viewtopic.php?p=280700#p280700
Dim Sh As Worksheet: Set Sh = ActiveSheet
Dim Lr As Long: Let Lr = 9
Dim rng As Range
 Set rng = Sh.Range("D7:D" & Lr)
 rng.Resize(, 5).Insert Shift:=xlToRight
End Sub

[YasserKhalil]

Yes Hans' trick does what I need exactly and thanks a lot for the contribution.

[Doc.AElstein]

OK