Add Rows Macro

jakek

New Member
Joined
Mar 22, 2009
Messages
1
I am looking to use a macro to add rows to a worksheet. the number of rows that i want to add in between each record is based on a value in one of the cells for that record.

for example:
joe blow|3

this would add 3 rows after this row. I am not that savy with vba. i have some code now that i can kind of follow what its suppose to do but when i run it, nothing happens.

the code i have i downloaded off the net. my variable number to use to add rows in my spreadsheet is listed in column H:

Code:
Sub splenda()
'Capture row of last entry in column H
ctrlValueRow = Cells(Rows.Count, "h").End(xlUp).Row
'Loop upward thru cells in ColH; insert as needed
Do While ctrlValueRow > 0
Cells(ctrlValueRow, 8).Activate
If ActiveCell.Value > 1 Then
For i = 1 To ActiveCell.Value
ActiveCell.Offset(1, 0).EntireRow.Insert
Next i
End If
ctrlValueRow = ctrlValuRow - 1
Loop
End Sub
 
Last edited by a moderator:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
jakek,

Welcome to the MrExcel board.

Try:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).


Code:
Option Explicit
Sub InsertRows()
Dim NR As Long, i As Long
For i = Cells(Rows.Count, 8).End(xlUp).Row To 1 Step -1
  If Cells(i, 8).Value <> "" And Cells(i, 8).Value <> 0 Then
    NR = Cells(i, 8).Value
    Range(Cells(i + 1, 8), Cells(i + 1 + NR - 1, 8)).EntireRow.Insert
  End If
Next i
End Sub


Then tun the "InsertRows" macro.


Have a great day,
Stan
 
Last edited:
Upvote 0
jakek

Welcome to the MrExcel board!

Here is a slightly different version. It uses your basic structure and ideas, with a bit of Stan's idea as well as some of my own.

I am a little unclear about the exact requirement though. Your example says you want 3 rows added when the column H value is 3. However, your code line
Code:
If ActiveCell.Value > 1 Then
would indicate that you don't want 1 row added if the column H value is 1. My code will add 1 row if column H is 1 but can be tweaked if that is not what youn want.

Also, it is a good idea to alway have 'Option Explicit' at the top of your module. That will force you to 'Dim' each variable which is a good idea and also help pick up spelling errors in your variables that will affect your code outcome. (You have used ctrlValueRow in most places, but ctrlValuRow - no e - in your third last line.)

That is why (at least one reason) your code wasn't working. First time through the loop ...
ctrlValueRow = ctrlValuRow - 1 = 0 - 1 = -1
(because ctrlValuRow was not defined and given a value it will be 0)


ctrlValueRow = ctrlValuRow - 1
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top