How to Delete blanks between a range and populate only the names inthe given range

[Yuvraj]

Hi All,

I need to know a way where I can key in a data validation in cell.

I have a range A1: A45

I have names in it but with blanks in between them

I cannot delete the blanks as it is a business requirement. But the
validation which I have added to a group of cells which on click of
drop down should only get the names from A1:A45 and not display the
blanks in between as it looks awkward.

Questions:

Can this be done with the help of a single excel function
or there is any other way to do this via code.

Kind Regards,

Yuvraj

 

[abe1952]

I don't deal much with VBA so I'll try to help you by guiding you through
various simple but logical steps, the way I would do it myself.

1. I would copy the names from cells A1 to A45 into a helper column way into
the right, say column Z, or somewhere closer if you don't mind this new
column being seen.

2. Having copied the names into cells Z1 to Z45, and while that range is
still selected, I would sort this range A to Z. This would bring all the
names up to cells Z1 to Z23.

3. I would then select cells Z1 to Z23 and assign a name to it, say "List"
and save the workbook.

4. Then where the drop-down cells are, I would edit the Data Validation by
typing "=List" (without the quotation marks) into the Source box and click OK.

 

[Gord Dibben]

You could copy the cells to another worksheet if you wanted before doing the
sort and naming the range.


Gord Dibben MS Excel MVP