Excel 2003: lines' insertion by a macro

[Kimed]

Hi,

I have a sheet with a fragment that looks like this:

          A:         B:
line1: +--BIG--+    cellB1
line2: | MERGED|    cellB2
line3: +--Cell-+    cellB3
line4:           =sum(B1:B3)

Now, if I *manually* select the whole line 3, copy it, and then do "Insert Copied Cells" on the same line, the line will be copied like this:

          A:         B:
line1: +--BIG--+    cellB1
line2: | MERGED|    cellB2
line3: +  Cell +    cellB3
line4: +-------+    cellB3
line5:           =sum(B1:B4)

The merged cell A1-3 will appropriately grow, and the formula range will include the added line as well. That's normal. If I issue following commands in a macro, though:

Rows("3:3").Select
Selection.Copy
Selection.Insert Shift:=xlDown

the result will be different:

          A:          B:
line1: +--BIG--+    cellB1
line2: | MERGED|    cellB2
line3: +--Cell-+    cellB3
line4: +--BIG--+    cellB1
line5: | MERGED|    cellB2
line6: +--Cell-+    cellB3
line7:           =sum(B4:B6)

How should I write the macro so that insertion would work the 'right' way?

Thanks

 

[combo]

With Rows("3:3")
    .Copy
    .Insert Shift:=xlDown
End With

combo

 

[Kimed]

Thanks, it worked. Weird, though, I've already tried commands like

Rows("3:3").Copy
Rows("3:3").Insert Shift:=xlDown

earlier, without WITH/END WITH. It gave me a runtime error 1004 on Insert command, so I had to do it longer way, which at least produced the desirable result... until I bumped onto a merged cell issue.

Thanks again.

 

[combo]

The With ... End With structure does not change anything. Your code works with active sheet (no worksheet reference), the row culd not either be copied or pasted in existing conditions.

combo

 

[SkipVought]

FYI, merged cells wreak all sorts of coding havoc. I would avoid if at all possible.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}