<div>
Hi ,<br />
<br />
Please refer the below link <br />
<br />
<b><a href="http://www.cpearson.com/excel/NoBlanks.aspx" rel="ugc">http://www.cpearson.com/excel/NoBlanks.aspx</a></b>
</div>


<div>
Rajul,<br />
<br />
that link does not answer my question. &nbsp; please see my question in the attachment file i uploaded in my original post.
</div>


<div>
Hi Flora,<br />
<br />
First you need to create a separate list in another column, I selected H in attached added below formula:<br />

<pre><code class="code-1 nolinks" id="code-20-42067877-1"><span>=IFERROR(INDEX($A$2:$A$100,SMALL(IF(ISTEXT($A$2:$A$100),ROW($A$1:$A$99),&quot;&quot;), ROW(A1))),&quot;&quot;)</span>
</code></pre>
Then Create a New NameRange, I created Names with below formula:<br />

<pre><code class="code-1 nolinks" id="code-20-42067877-2"><span>=OFFSET(Data!$H$2,0,0,COUNTIF(Data!$H$2:$H$100,&quot;&gt;&quot;&quot;&quot;))</span>
</code></pre>
Then in your Data Validation List =Names<br />
Please find attached for your reference.<br />
Hope this helps<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w13/1153395/Flora_Data-Validation-Without-Blank.xlsx" target="_blank" class="file-inline" title="Data Validation (13 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Flora_Data-Validation-Without-Blank.xlsx</a>
</div>


Flora_Data-Validation-Without-Blank.xlsx

<div>
Shams, thanks. can this be done without use of helper column?
</div>


<div>
Hi Flora,<br />
<br />
Regrettably no
</div>


<div>
@Flora<br />
You can use VBA to get the desired drop down list.<br />
Place the below code on Data Sheet Module and it will insert a drop down validation in column D Row2 downward when a cell is selected.<br />
<br />
See if this is something you can work with.<br />

<pre><code class="code-10 nolinks" id="code-20-42068054-1"><span>Private Sub Worksheet_SelectionChange(ByVal Target As Range)</span>
<span>If Target.CountLarge &gt; 1 Then Exit Sub</span>
<span>Dim ws As Worksheet</span>
<span>Dim tbl As ListObject</span>
<span>Dim dict, x</span>
<span>Dim i As Long</span>
<span></span>
<span>If Target.Column = 4 And Target.Row &gt; 1 Then</span>
<span>    Set ws = Sheets(&quot;Data&quot;)</span>
<span>    Set tbl = ws.ListObjects(1)</span>
<span>    x = tbl.DataBodyRange.Columns(1).Value</span>
<span>    Set dict = CreateObject(&quot;Scripting.Dictionary&quot;)</span>
<span>    </span>
<span>    For i = 1 To UBound(x, 1)</span>
<span>        dict.Item(x(i, 1)) = &quot;&quot;</span>
<span>    Next i</span>
<span>    </span>
<span>    On Error Resume Next</span>
<span>    With Target.Validation</span>
<span>        .Delete</span>
<span>        .Add _</span>
<span>            Type:=xlValidateList, _</span>
<span>            AlertStyle:=xlValidAlertStop, _</span>
<span>            Formula1:=Join(dict.keys, &quot;,&quot;)            </span>
<span>    End With</span>
<span>End If</span>
<span>End Sub</span>
</code></pre>
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w13/1153417/DropDownList.xlsm" target="_blank" class="file-inline" title="DropDownList.xlsm (18 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>DropDownList.xlsm</a>
</div>


DropDownList.xlsm

<div>
Shums pointed out a bug in the proposed code that in the drop down list a single name is changed into two items because the lastname and firstname are separated by a comma. Thanks Shums!<br />
<br />
Here is a workaround, place both the code on Data Sheet Module and the drop down list will show the names without a comma separating the lastname and the first name but when you pick a name from the drop down, lastname and the firstname will be separated by a comma again.<br />

<pre><code class="code-10 nolinks" id="code-20-42068143-1"><span>Private Sub Worksheet_Change(ByVal Target As Range)</span>
<span>If Target.CountLarge &gt; 1 Then Exit Sub</span>
<span>If Target.Column = 4 And Target.Row &gt; 1 Then</span>
<span>    If Target &lt;&gt; &quot;&quot; Then</span>
<span>        Application.EnableEvents = False</span>
<span>        Target = WorksheetFunction.Substitute(Target.Value, &quot; &quot;, &quot;, &quot;, 1)</span>
<span>        Application.EnableEvents = True</span>
<span>    End If</span>
<span>End If</span>
<span>End Sub</span>
<span></span>
<span>Private Sub Worksheet_SelectionChange(ByVal Target As Range)</span>
<span>If Target.CountLarge &gt; 1 Then Exit Sub</span>
<span>Dim ws As Worksheet</span>
<span>Dim tbl As ListObject</span>
<span>Dim dict, x</span>
<span>Dim i As Long</span>
<span></span>
<span>If Target.Column = 4 And Target.Row &gt; 1 Then</span>
<span>    Set ws = Sheets(&quot;Data&quot;)</span>
<span>    Set tbl = ws.ListObjects(1)</span>
<span>    x = tbl.DataBodyRange.Columns(1).Value</span>
<span>    Set dict = CreateObject(&quot;Scripting.Dictionary&quot;)</span>
<span>    </span>
<span>    For i = 1 To UBound(x, 1)</span>
<span>        dict.Item(Replace(x(i, 1), &quot;,&quot;, &quot;&quot;)) = &quot;&quot;</span>
<span>    Next i</span>
<span>    </span>
<span>    On Error Resume Next</span>
<span>    With Target.Validation</span>
<span>        .Delete</span>
<span>        .Add _</span>
<span>            Type:=xlValidateList, _</span>
<span>            AlertStyle:=xlValidAlertStop, _</span>
<span>            Formula1:=Join(dict.keys, &quot;,&quot;)            </span>
<span>    End With</span>
<span>End If</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
Thanks Neeraj for coming up with VBA approach, it works perfect now.
</div>


<div>
Thanks for confirming Shums!<br />
Hope it would work for Flora as well.
</div>


<div>
thank you Subodh Tiwari.<br />
<br />
i paste the code in Data sheet and it works just fine until you save and close the workbook and once you reopen the workbook. the workbook crashes and everything is lost.<br />
<br />
the code makes the workbook corrupt. <br />
<br />
you can try it on my file Flora_Data-Validation-With<wbr />out-Blank.<wbr />xlsx
</div>


<div>
Please find the corrected workbook.<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w13/1153448/DropDownListNewFile.xlsm" target="_blank" class="file-inline" title="DropDownListNewFile.xlsm (17 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>DropDownListNewFile.xlsm</a>
</div>


DropDownListNewFile.xlsm

<div>
Thanks.<br />
<br />
the macro works.
</div>


<div>
untick Remove personal information was the culprit. i unmarked it and it worked.
</div>


<div>
You're welcome Flora! Glad it worked.
</div>


<div>
<div class="content wysiwyg-content">
I have the attached workbook. <br />
<br />
i have used the formula in the dropdown list of data validation list.<br />
<br />
as you can see if my data has blank cells they still appear in the dropdown. how can this be modified so that it does not list the blank cells in the dropdown?<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w13/1153388/Book.xlsx" target="_blank" class="file-inline" title="Book.xlsx (11 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Book.xlsx</a>
</div>
</div>


Book.xlsx

I have the attached workbook. 

i have used the formula in the dropdown list of data validation list.

as you can see if my data has blank cells they still appear in the dropdown. how can this be modified so that it does not list the blank cells in the dropdown?

how to ignore blank cells from the data validation list?

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.