Add/subtract rows and Name all from a variable range

[leaning]

OK, hopefully this is the last question from me for today:

1. For the attached, for RoyUK's code, Add a Row needs to add ONLY to the bottom of the blue range and push the lower red range down. I can't figure out the syntax for how to limit it to just that range. Right now, the code looks for the first empty row and adds there.

2. Also for the column A of that same growing/shrinking blue range, I added a Named Range (WONum) so that I can use those column A numbers as a Data Validation List in other sheets of the workbook. Right now, the named range isn't capturing the entire blue range as I add and delete rows.

3. I read a post about adding/deleting rows from named ranges. Would naming the blue range make the code easier?

4. The blue range should be unlocked, but all the red cells should be locked. Since the blue range is changing, the cell locking/unlocking needs to change also.

Many problems on this same sheet, but this template will be used for many others. I appreciate if anyone can help fix this.

Respectfully,

Lost

[leaning]

All,

Making progress on this scrambling together some code, but still some problems:

Add row code adds new row to top (vice bottom) of named range MyRange (=WSO!$6:$6), does not put borders around cells A:G, and does not put the sum D:F formula in the new row (it puts it down a row).

Please Login or Register  to view this content.

Data Validation dymanic named range WONum includes blanks and lists everything to the bottom of the sheet vice to the bottom of MyRange.

Formula: =OFFSET(WSO!$A$6,0,0,MATCH(REPT("z",255),WSO!$A$6:INDEX(WSO!$A:$A,ROWS(WSO!$A:$A))))

Any help in fixing these last few things appreciated! Thanks!

Respectfully,

Lost

[leaning]

All,


Still having problems figuring out how to add a row to a Named Range.

This code is adding to the bottom of used range rather than the bottom of the named range "MyRange":

Please Login or Register  to view this content.

Any help with this project appreciated!

Respectfully,

Lost

[snb]

Please Login or Register  to view this content.

[leaning]

snb,

1. My sheet is set up like an Oreo cookie. I need the filling (MyRange) to get bigger or smaller, not the cookie (top or bottom cells) part.

2. And then the validation list needs to reference the A column of the filling only.

??

Respectfully,

Lost

[leaning]

All,

This thread solution is summarized by the following thread:

http://www.excelforum.com/excel-prog...ot-blanks.html

At the bottom of that thread, you will find two workbooks: one which adds rows at the bottom of a sheet, and another which adds rows in a middle section of a sheet.

99.9% of the tough stuff was by virtue of the Excel geniuses in this forum. Thanks all for helping complete a project which will make work easier for many, many people!

Respectfully,

Lost