Help needed some basic stuff

[surbharti]

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
Is there i need to do another loop
or need to select second range value and last used row in formula?

kindly help

 

[StuLux]

I'm guessing that what you mean is that your data validation list contains blanks and this is presumably because your range sheet2!AH2:AH1000 has blanks. Instead of using a fixed range try using a dynamically named range (search Mr Excel if not sure how to create a dynamically named range) which will grow/shrink depending on the list of actual value sin your range.

 

[surbharti]

h

I'm guessing that what you mean is that your data validation list contains blanks and this is presumably because your range sheet2!AH2:AH1000 has blanks. Instead of using a fixed range try using a dynamically named range (search Mr Excel if not sure how to create a dynamically named range) which will grow/shrink depending on the list of actual value sin your range.

i thank you, this has been resolved