Excelnoub
Member
I am trying to find out, almost in every site, how to do the following.
This code is inserted in my Sheet4:
[pre]<br />
If Target.Count > 1 Then Exit Sub<br />
If Not Intersect(Target, Range("B5:B10000") Is Nothing Then<br />
Application.EnableEvents = False<br />
NumRows = Target.Value - 1<br />
For R = 1 To NumRows<br />
Target.Offset(1, 0).EntireRow.Insert<br />
Next R<br />
Application.EnableEvents = True<br />
End If<br />
[/pre]
The code adds extra rows when the user inserts a higher number then 001, 01 or 1. (Format).
In my sheet I have ranges, meaning the information inserted in the sheet is from row 5 to 30 and never outside.
I need to keep the range of data entry. Is there a way I can have the Target.Offset(1, 0).EntireRow.Insert function in accordance with this request?
Therefore if 4 rows are added (the user will insert 5 in column B) then delete 4 rows from row 30.
The reason why is that every time a number is inserted in Column B (Higher than 001) it adds a new row. Therefore making my rows going over the limit I need. So if the number 2 is inserted in Column B a new row is created making my worksheet “Page Break Preview” 31 instead of 30.
This code is inserted in my Sheet4:
[pre]<br />
If Target.Count > 1 Then Exit Sub<br />
If Not Intersect(Target, Range("B5:B10000") Is Nothing Then<br />
Application.EnableEvents = False<br />
NumRows = Target.Value - 1<br />
For R = 1 To NumRows<br />
Target.Offset(1, 0).EntireRow.Insert<br />
Next R<br />
Application.EnableEvents = True<br />
End If<br />
[/pre]
The code adds extra rows when the user inserts a higher number then 001, 01 or 1. (Format).
In my sheet I have ranges, meaning the information inserted in the sheet is from row 5 to 30 and never outside.
I need to keep the range of data entry. Is there a way I can have the Target.Offset(1, 0).EntireRow.Insert function in accordance with this request?
Therefore if 4 rows are added (the user will insert 5 in column B) then delete 4 rows from row 30.
The reason why is that every time a number is inserted in Column B (Higher than 001) it adds a new row. Therefore making my rows going over the limit I need. So if the number 2 is inserted in Column B a new row is created making my worksheet “Page Break Preview” 31 instead of 30.