Named range question

[hallingh]

Pretty simple question: Is there a way to make a named range ignore blank values? So if I had a named range defined as A1:A125, but A25:A50 were blank, it would just display A1:A24 and A51:A125?

THanks for the help.

Hank

 

[shg]

Yes. but it wouldn't be dynamic:

Range("A1:A125").SpecialCells(xlCellTypeConstants).Name = "Bob"

 

[hallingh]

Hmmm I was hoping to not use vba for this one. I have a range that only has a certain amount of values but some of them are currently blank. I did it this way so other people can simply copy and paste data into the range and the documents will still work. I don't want the blank documents to show, though, because I'm displaying the values of the named range in a combo box, and currently there are a bunch of blank values being displayed. I figured there would be a simple way using native excel to either have the named range ignore blank values or have the combo box ignore blank values, but I can't seem to figure out how to do either.

Is there any hope for what I'm trying to do?

Thanks for the response.

Hank

 

[shg]

Select A1:A125, Ctrl+G, Special Cells, Constants. Then you can give the range a name in the Names box, or copy it to someplace else.