Adding additional rows BEFORE first blank row (????)
 

Hello - I have an issue that I am sure a simple macro will resolve.
Unfortunately, I am still a newbie from a macro perspective.

I have a worksheet with 10 rows and 6 columns. The format of the
worksheet is very important, thus it is protected so as not to risk
users modifying it. Some users will have a need to increase the number
of rows provided. What I would like is to have a macro that will
automatically add 5 new rows at the bottom of the "input area". For
example, lets assume column A has various names within the specified
cells:

A1 = Doug
A2 = Joe
A3 = Dan
A4 =
A5 = Scott
A6 = Stan

The key here is that I need to build a macro that will insert these
five rows between A3 and A4. There will always be a blank row between
the two sets of data (A4 in this example). Thus, the key is for a macro
to insert 5 new rows above the first "blank" row.

Any thoughts?

Adding additional rows BEFORE first blank row (????)
 

try this
Sub add5rowsatblank()
nr = Cells(1, "a").End(xlDown).Row
Rows(nr + 1 & ":" & nr + 4).Insert
End Sub

--
Don Guillett
SalesAid Software

"JoeJoe" wrote in message
oups.com...
Hello - I have an issue that I am sure a simple macro will resolve.
Unfortunately, I am still a newbie from a macro perspective.

I have a worksheet with 10 rows and 6 columns. The format of the
worksheet is very important, thus it is protected so as not to risk
users modifying it. Some users will have a need to increase the number
of rows provided. What I would like is to have a macro that will
automatically add 5 new rows at the bottom of the "input area". For
example, lets assume column A has various names within the specified
cells:

A1 = Doug
A2 = Joe
A3 = Dan
A4 =
A5 = Scott
A6 = Stan

The key here is that I need to build a macro that will insert these
five rows between A3 and A4. There will always be a blank row between
the two sets of data (A4 in this example). Thus, the key is for a macro
to insert 5 new rows above the first "blank" row.

Any thoughts?

Adding additional rows BEFORE first blank row (????)
 

Try this little snippet of code:


Sub Insert5RowsAboveFirstBlankRow()

'Your post implied you wanted to insert 5 rows at the first blank row

Dim rng As Range

Set rng = Range("a1").End(xlDown).Offset(1, 0)
Set rng = Range(rng, rng.Offset(4, 0))
rng.EntireRow.Insert

End Sub


Sub Insert5RowsAboveLastBlankRow()
'If you wanted to insert 5 rows after the last blank row

Dim rng As Range

Set rng = Range("a65000").End(xlUp).End(xlUp)
Set rng = Range(rng, rng.Offset(4, 0))
rng.EntireRow.Insert

End Sub


Good luck and happy programming

LooneyTunes


JoeJoe wrote:
Hello - I have an issue that I am sure a simple macro will resolve.
Unfortunately, I am still a newbie from a macro perspective.

I have a worksheet with 10 rows and 6 columns. The format of the
worksheet is very important, thus it is protected so as not to risk
users modifying it. Some users will have a need to increase the number
of rows provided. What I would like is to have a macro that will
automatically add 5 new rows at the bottom of the "input area". For
example, lets assume column A has various names within the specified
cells:

A1 = Doug
A2 = Joe
A3 = Dan
A4 =
A5 = Scott
A6 = Stan

The key here is that I need to build a macro that will insert these
five rows between A3 and A4. There will always be a blank row between
the two sets of data (A4 in this example). Thus, the key is for a macro
to insert 5 new rows above the first "blank" row.

Any thoughts?