Remove spaces from drop down list (1 Viewer)

suryu

Member
Local time
Tomorrow, 04:15
Joined
Apr 3, 2020
Messages
86
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

Enthusiastic Amateur
Local time
Today, 22:45
Joined
Sep 21, 2011
Messages
14,048
Do not put it there in the first place?

Clean the data in your range.
 

suryu

Member
Local time
Tomorrow, 04:15
Joined
Apr 3, 2020
Messages
86
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

Enthusiastic Amateur
Local time
Today, 22:45
Joined
Sep 21, 2011
Messages
14,048
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.?
 

Users who are viewing this thread

Top Bottom