Insert blanks, but populate new blank rows in one column

Pierre

unread,

Jul 29, 2010, 3:08:24 PM7/29/10

to

I'm using this code to add 3 blank rows between existing records. How
might I populate the new cells between records just in column B with a
predefined group of values located on another worksheet? These will
not change between rows, but need to be inserted in all the new rows,
in column B. Have tried highlighting the column, F5 for going to
special, blanks, and pasting. . .it works intermittently (getting
frequent paste same size and shape errors), and I'm afraid I need
something more reliable. Thoughts? Many thanks.

Pierre

Sub Insert3Rows_into_927ver2()
Application.ScreenUpdating = False
Sheets("927VER2").Select
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 2 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True

End Sub

Jim Cone

unread,

Jul 29, 2010, 8:49:24 PM7/29/10

to

Sub Insert3Rows_into_927ver_R1()

Application.ScreenUpdating = False
Sheets("927VER2").Select
Dim numRows As Integer
Dim r As Long

Dim vArr() As String
Dim rRows As Range

ReDim vArr(1 To 3, 1 To 1)
vArr(1, 1) = "Larry"
vArr(2, 1) = "Moe"
vArr(3, 1) = "Curly"

r = Cells(Rows.Count, "A").End(xlUp).Row - 1

numRows = 3
For r = r To 2 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert

Set rRows = Range(Rows(r + 1), Rows(r + 3)).Columns
rRows.Columns(2).Value = vArr()

Next r
Application.ScreenUpdating = True
End Sub

--
Jim Cone
Portland, Oregon USA
xlCompanion Excel add-in: http://tinyurl.com/XLCompanion

.
.
.

"Pierre" <cow...@aol.com>
wrote in message

Pierre

unread,

Jul 29, 2010, 9:47:37 PM7/29/10

to

Jim, aside from nailing this one to a T, (it works absolutely
perfectly), you soitenly made my day with your choice of woids to
insert into the fields. Sometimes, I just try to think and nuthin'
happens. Thanks so much for the rescue.

Pierre

Jim Cone

unread,

Jul 29, 2010, 11:53:08 PM7/29/10

to

You are welcome.
'--
Jim Cone

"Pierre" <cow...@aol.com> wrote in message news:5e3392ee-d490-4e32...@u26g2000yqu.googlegroups.com...

Pierre

unread,

Jul 30, 2010, 9:50:25 AM7/30/10

to

John,

One little thing. .

It misses loading the very last "Parent" record, and doesn't populate
the rows following it with the group of 3, just on that one. Can you
revisit, and tell me what to modify?

Many thanks again.

Pierre

On Jul 29, 10:53 pm, "Jim Cone" <james.cone...@comcast.netXXX> wrote:
> You are welcome.
> '--
> Jim Cone
>

> "Pierre" <cow...@aol.com> wrote in messagenews:5e3392ee-d490-4e32...@u26g2000yqu.googlegroups.com...

> > xlCompanion Excel add-in:http://tinyurl.com/XLCompanion- Hide quoted text -
>
> - Show quoted text -

Jim Cone

unread,

Jul 30, 2010, 10:31:34 AM7/30/10

to

Remove the "- 1" at the end of...

r = Cells(Rows.Count, "A").End(xlUp).Row - 1

"Pierre" <cow...@aol.com>
wrote in message
news:f7212a18-efff-463b...@i28g2000yqa.googlegroups.com...
Jim,

Pierre

unread,

Jul 30, 2010, 10:36:27 AM7/30/10

to

Thank you!

Pierre

On Jul 30, 9:31 am, "Jim Cone" <james.cone...@comcast.netXXX> wrote:
> Remove the "- 1" at the end of...
> r = Cells(Rows.Count, "A").End(xlUp).Row - 1
>
> "Pierre" <cow...@aol.com>

> wrote in messagenews:f7212a18-efff-463b...@i28g2000yqa.googlegroups.com...