Drop down list without blank cell

[elsuji]

Dear Team,

In my workbook i am having 3 work sheet (Sheet1, Data & Test)

I am having date list in worksheet Test.

I want that dates as drop down list in worksheet Sheet1 Training Date.

I attached my sample file here

Kindly help me to solve this issue

[Leith Ross]

Hello elsuji,

This requires a few steps to accomplish. First, you need either a UDF macro or formula to create a blank free list. Second, you need a Named Range to hold the blank free list.

In the attached workbook I have created a new Named Range called Training_Dates. This has a formula to list the array results of the UDF macro NoBlanks. The formula starts in Data!$C$2 and ends at Data!$C$17. If you need more room for dates, just drag the formula down further.

The formula is...


=IFERROR(INDEX(NoBlanks(Test!$A$2:$A$1000),ROW($A1)),"")



Here is the UDF macro to remove the blanks from either a single row or column of cells.


Function NoBlanks(ByRef Rng As Range)


Dim counter As Long
Dim Item As Variant
Dim outData As Variant
Dim rngData As Variant

Application.Volatile

If (Rng.Rows.Count > 1 And Rng.Columns.Count > 1) Or (Rng.Cells.Count = 1) Then
NoBlanks = CVErr(xlErrRef)
Exit Function
End If

rngData = Rng.Value

If Rng.Columns.Count > 1 Then
ReDim outData(1 To 1, 1 To UBound(rngData, 2))
Else
ReDim outData(1 To 1, 1 To UBound(rngData, 1))
End If

For Each Item In rngData
If Not IsEmpty(Item) Then
counter = counter + 1
outData(1, counter) = Item
End If
Next Item

ReDim Preserve outData(1 To 1, 1 To counter)

If Rng.Columns.Count > 1 Then
NoBlanks = outData
Else
NoBlanks = Application.Transpose(outData)
End If

End Function

[elsuji]

Dear Leith Ross,

I tried your formula on my sheet. But it is not working.

i attached my sheet here for your reference. Can you please check the sheet

[Leith Ross]

Hello elsuji,

I have attached an updated copy of your workbook.