Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically adding additional rows | Excel Discussion (Misc queries) | |||
Adding additional rows with formula | Excel Programming | |||
AUTO FORMATTING WHEN ADDING ADDITIONAL ROWS | Excel Programming | |||
Adding additional rows in VBA based on character strings | Excel Programming | |||
adding blank rows | Excel Programming |