increase range size

[still learning]

Hi
I have a list box that uses a range name in the “row source”
What I do now is make the range have empty cells and if I have to increase the size after that, I make a new range.
Example:
A1:A20 = range ”test”
A10:A20 is blank
If I have to add past A20, I re-make the range.
I wondering if there is a way to increase the range as I add names.

mike

 

[myall_blues]

Define the 'Refers to' part using an OFFSET formula and it will grow as you add elements.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),1)

 

[still learning]

Hi Murry,
I'm doing something wrong
A1:A10 = "test"
I bring up the name manager
I post your formula in the refers to part
I put something in A11
I open the userform and the list box only goes to A10
The list box used "test" as row source

It's getting late for me....I

mike

 

[myall_blues]

Is it a data validation list box?

Use =test as your row source. Alternatively, you can use the offset formula directly as the row source for the data validation.

 

[still learning]

Adding to what I tried:
I notice that A1:A10 no longer has the range name "test".
My userform list box row source gives an error that there is no range name "test"
If I try F5 ..range name "test" is not valid.
When I open the name manager..What I pasted showed up as

="  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),1) "

I'm missing something !!!!!!

mike

 

[still learning]

no I use CTRL + F3 to bring up the name manager
I never used the data validation list box

I'm going to have to come back tomorrow

thank you for your help. I hope you'll be here tomorrow to continue

mike

 

[myall_blues]

You’ve pasted it in wrong. Start fresh.

Formulas ->Name manager -> Select whatever is there and delete it.

Then in the Name manager box, click new.

Add the details - no quotes involved. Click OK

 

[still learning]

Murry.
I saw that you have replied after I logged off.
IT WORKS ......
Thank you, thank you
Every time I pasted the formula, it added an equal sign and quotes.
I ended up typing it in

I will be changing a couple of userforms now

I also have something new to look at...for me. anyway ( see my name)

Again..thank you for your help and Patience

mike

 

[myall_blues]

You’re welcome. Thanks for the feedback.

 

[Alex Blakenburg]

Since you have MS365, if you do not have any users using older versions of Excel, you could take advantage of the new TrimRange option.
Either as a range name or directly in the Data Validation box you could put this:

$A:.$A

If the range doesn't start in A1 then perhaps:
(You can trim from both the top and the bottom on a whole column reference but that won't work if you have anything above the list that you want included)

$A10:.$A1000