insert rows automatically

sbrown64

Board Regular
Joined
Aug 23, 2019
Messages
87
HiI have created a table & in column B I enter a number (i.e 3) is there a way to automatically add 2 rows below the line, so if the number was 5, it would add 4 rows below?RegardsStephen
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/13/2019  10:05:27 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 And IsNumeric(Target.Value) Then Rows(Target.Row).Offset(1).Resize(Target.Value - 1).Insert Shift:=xlDown
Application.EnableEvents = True
End Sub
 
Upvote 0
Well it works for me. I test all my scripts.
Did you install the script as I explained.

And you entered a number like 3 in column B

And your workbook is Macro Enabled.
 
Upvote 0
I di everything you mentioned

here is the link to the file

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://1drv.ms/x/s!Aq3WqOz73fYygYUnHWHhyScSho9udw?e=OCTc1u

Regards
Stephen
[/FONT]
 
Upvote 0
I never click on links or open files posted here:

We are talking about Column(2) of your worksheet.
 
Upvote 0
yes, column B is where I put a number and when I tab across from it, nothing happens. So if I put 3 on line 50, I would want it to add two more lines below and then I can fill in the data and then on line 53 I may put the number 1, so not needing another line.

04/08/201931BRISTOL CITY A31HERNANDEZ
BAMFORD
HARRISON
10/08/201911NOTTINGHAM FOREST H1 1HERNANDEZ
17/08/201920WIGAN A31BAMFORD
BAMFORD
21/08/201910BRENTFORD H31NKETIAH
24/08/201930STOKE CITY A31DALLAS
ALIOSKI
BAMFORD
31/08/201901SWANSEA H0 1





<colgroup><col><col span="2"><col><col span="6"></colgroup><tbody>
</tbody>


<colgroup><col><col span="2"><col><col span="6"></colgroup><tbody>
</tbody>
 
Last edited:
Upvote 0
Did you get any error messages?
 
Upvote 0
I really do not know what to say.

If you enter 4 into Range("B4") for example there should be 3 rows inserted below Row(4)

This must be done manually not the result of a formula causing the change.
 
Upvote 0
i saved your code to the worksheet page, not a mudule and when i go to the next line. i entered a date and then tabbed to column B and entered number 6 using the keyboard and nothing happened.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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