named range

[orsm6]

Hi, i am trying to define a named range that excludes any blank cells. the named range though is across columns. the range is D11:IS11. I wanted to then use a DV list box to link to the named range.

i have found plenty of examples that will do this in a column, how can i do it in one row across columns?

TIA

 

[Rick Rothstein]

If D11:IS11 only contain constants (no formulas)...

Range("D11:IS11").SpecialCells(xlConstants).Name = "NewNameForRange"

 

[orsm6]

hi Rick, thanks for your help.

when i paste your formula into a new named range it double quotes some of it.
i run into trouble when trying to create a data validation box called list. i get an error.

="Range(""c11:c11"").SpecialCells(xlConstants).Name = ""list"""

cheers.

also. this range contains names only. each cell has last name, first name over 2 lines...e.g.

last name,
first name

 

[orsm6]

my question was possibly misunderstood. i don't want to use a macro.

in cell B11 i want to create a drop down list. just like with a normal data validation list box.
the range though is D11:IS11
how can i get b11 to show all the names in that range, excluding the blank cells

 

[Rick Rothstein]

my question was possibly misunderstood. i don't want to use a macro.

in cell B11 i want to create a drop down list. just like with a normal data validation list box.
the range though is D11:IS11
how can i get b11 to show all the names in that range, excluding the blank cells

I do not believe you can do that (other than with a macro) with the version of Excel I use (XL2010), but perhaps the newer versions have a function (TEXTJOIN maybe) that can be used. You will have to wait for someone with a newer version of Excel to come along and respond.

 

[orsm6]

i appreciate your help. i will see if i can get your idea working in the meantime using VBA.

ta

 

[orsm6]

I have found a way to do it.

i created a named range called LIST: =SOP!$D$11:INDEX(SOP!$D11:$IS11,COUNTA(SOP!$D11:$IS11))
i then created a data validation list box using =LIST
i now have a list of names in B11 that are contained in range D11:IS11 and it excludes blank cells