Adding row to a named range-updating the named range address

I have a file to keep records of RV park guest.
I use a row of data range named GuestIP where I enter the data for each new guest.

I then use the following commands to add the guest to range named Guest_DB.

Please Login or Register  to view this content.

However this does not change the address of the Guest-DB to include the added guest.

ie Before adding the new guest the Guest-DB address is C6:AB564, after updating the address is still C6:AB564 needs to be C6:AB565.

vlookup commands else where in the workbook cannot find the new guest until I manually change the Address to include the row with the new guest.

Is there a way to change the range address when you add a row to the bottom of the range?

Any help would be appreciated.

Hi..

I then use the following commands to add the guest to range named Guest_DB.

Your sheet is named "Guest_DB".. so i assume that that is a 'typo'... and therefore assume your Named Range is called "GuestIP"?

Here's a way you can increase the size(by 1 row) of your Named Range...

Please Login or Register  to view this content.

It basically 're-adds' your named range and increases the row size by 1...
Does that make sense?

Not sure if that's what I'm looking for.

The Name "Guest-DB" was an error.
The worksheet sheet name is "Guest-DB", but
The range I want it to add to is located on worksheet "Guest-DB" range name is "GuestData".


GuestIP is a single row where I manually enter some of the information, other parts of the row calculate information. Once the information has been verified I add the values to the "GuestData" range.

I don't want the calculation "formulas" from the input range "GuestIP" in the output range "GuestData",
just the values.
I want each record in that, ("GuestData"), range unchanged after it's been added.

vlookup formulas else where in the workbook look up "GuestData" for information about a guest.

If the line you show just changed "GuestData" by one line and that all it does, I could use it.

Thank you
JWD

Ok, I tried that line as a stand alone to see what it did. I had to add sheets id in front of the .range to make it work. but that would solve my problems

Thank You very much
JWD

Hi.. yeap .. sorry.. that line of code would normally be used like:

Please Login or Register  to view this content.

I used this script and it appeared to work.
However after several entries I discovered the Range address as not 'Adding' the new line it was shifting the Range name down by one line.
In a different file the Range name "ProLst" starts in cell B2 and ends in cell K79,
When I use the following commands:

Please Login or Register  to view this content.

to add a new item to the list, the new range for 'ProLst' is now B3 thru K80, not the intended B2 thru K80.
I lose the first row of data in the file.

I need the range name to increase by one row of data, not just change the location of the range.

Any help would be appreciated.
Thank you

Hi..

Oops..
I may have led you astray previously..

This will increase the range by 1 row..

Please Login or Register  to view this content.

Thank You, I'll try it today.

Change my code and this one worked
Thank You