Hi,

This will copy the named range 'Test' to D1000. It will then delete any blank rows in the 200 row list. Then it names the new list 'Test2' which you can use in your Data Validation.

[VBA]
Sub KillBlanks()
Dim i As Long
Dim lastrow As Long
'Speed
Application.ScreenUpdating = False

'Copy named range to somewhere
Range("Test").Copy Range("D1000")
'check 200 rows of named range

For i = 1200 To 1000 Step -1
If Cells(i, "D") = "" Then
'Is blank
Cells(i, "D").EntireRow.Delete
End If
Next i
'Get end of new list
lastrow = Range("D" & Rows.Count).End(xlUp).Row

'Name it 'test2'
With ActiveWorkbook
On Error Resume Next
.Names("test2").Delete
On Error GoTo 0
'//Note R1C1 notation of new location!
.Names.Add Name:="Test2", RefersToR1C1:="=Sheet1!R1000C4:R" & lastrow & "C4"

End With

'Reset
Application.ScreenUpdating = True

End Sub

[/VBA]