Delete Blanks in List Box Form Control

[Pjam]

Hi,

Is there a way to format the List box form control so that the
validation range does not include blanks?

Im using this and it does not seem to work

=OFFSET(Assumptions!$AF$2,0,0,COUNTIF(Assumptions!$AF$2:$AF$5000,"<>"),1)

 

[Sthrncali]

Hi,

Is there a way to format the List box form control so that the
validation range does not include blanks?

Im using this and it does not seem to work

=OFFSET(Assumptions!$AF$2,0,0,COUNTIF(Assumptions!$AF$2:$AF$5000,"<>"),1)

Are you just trying to load all values from range "AF2:AF5000" skipping any blank cells?

If so, just fill it with VBA...

i.e.

Dim myRange as Range
Dim mySheet as Worksheet

Set mySheet = Sheet1 'Change this to your sheet number
Set myRange = mySheet.Range("AF2:AF5000")

myListBox.clear

For each cell in myRange
If cell.value <> "" then
With myListBox
.add item cell.value
End With
End if
Next Cell