Deleting rows in a range VBA 2007

[Excelnoub]

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&#34) 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.

 

[SirJB7]

Hi, Excelnoub!


First of all, what you're doing "is called cross-posting and is generally frowned upon in the Blogosphere as it causes people to potentially waste our time when a question is already answered. You should also check and respond to posts and let posters know if they are heading in the write direction or not." Hui's dixit, SIC. And I agree 101%.


If you'd have read the first three green sticky posts at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker."


"If you do cross post, please put that in your post." <----------


"Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."


Now, let us focus on your issue. I have a few questions about it:

0) in which cell do you input the value? in range B5:B30 or from B31 in advance?

1) where do you have that code in worksheet Sheet4? within which procedure?

2) do you have any previous statements in that procedure? specially On Error or Resume

3) tried changing first line to this? (even I don't think it'd help, but...)

If Target.Cells.Count > 1 Then Exit Sub

4) what do you mean by adding 4 rows (where? see 0) and then deleting 4 rows from row 30 (from 27 to 30 or from 31 to 34)?


Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!