Remove spaces from drop down list

[suryu]

Anybody tell me how to delete spaces from drop down list .
i did code like this:

with sh1.range("A2").validation
.delete
.Add type:xlvalidateList, AlertStyle:=xlValidAlertStop, operator:xlBetween, Formula:"=sheet2!AH2:AH1000"
.IgnoreBlank=True
.IncellDropDown=True
.InputTitle=""
.InputMessage= ""
.ShowInput= True
.ShowError= True

End With


Using this code i am getting blank in drop down, how to remove blank

 

[Gasman]

Do not put it there in the first place?

Clean the data in your range.

 

[suryu]

Do not put it there in the first place?

Clean the data in your range.

is there i need to start loop again to clean data?

 

[Gasman]

Well I was thinking of actually cleaning the data at the source, else you will need to clean every time you run this code? Up to you.
You must have cells in that range that have something in them that makes them not blank so the IgnoreBlank does not work.?

You delete the existing validation before repopulating, so I would say Yes.?