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?
|