Data Validation List with no empty cells

[YasserKhalil]

Hi everybody
I have a named range "Test" for the range A2:A100 and there are empty cells in my range.
When I made a data validation list for the range "Test" the empty cells are embedded in the list
I want to get rid of the empty cells from my list

[austenr]

you want to delete the empty cells or ignor them in the validation? if you want to ignore them in the validation there is a check box to ignore blanks in data validation.

[YasserKhalil]

Thanks for reply
I actually check the option Ignore Blanks but the list is still has empty cells
I want not to imply the empty cells in the list

[YasserKhalil]

UP

[rbrhodes]

Hi,

You could sort the list then redo the dat validation for the new last row. or a macro to delete blank rows or and advanced fiter (unique, in place)...

[YasserKhalil]

I tried advanced filter and I can't delete blank cells as they have formulas..
I want to maintain the structure of my table so I don't want to sort it
can you provide me with a macro that extract the values without blanks to another column?

[YasserKhalil]

If there is no way to do this, please tell me to stop posting

[rbrhodes]

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.


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

[YasserKhalil]

Thanks a lot Mr. rbrhodes
It works fine but the code is somewhat long...
I hope I find a solution with formulas..
What if we extract the values except empty cells to another helper column??
Is it possible by formulas?