List Box RowSource VBA Array Restrict # Rows

[michaeldh]

Hi,

I have a mutlidimensional array that fills a listbox. It is an array as follows:

vArray(1 to 65536)

The list box basically has thousands of rows of blank data in it as a result of:

Listbox1.list = vArray

Is there a way to make the listbox not show all the blank data and to only show the rows that contain a value.

Thanks.

Michael.

 

[mikerickson]

Dim oneElement as Variant

With ListBox1
    For each oneElement in vArray
        If oneElement <> vbNullString then ListBox1.AddItem oneElement
    Next oneElement
End With

I'm tempted to ask, why such a huge range with so little data scattered about that empty space.

How many non-blank elements are in your list box?
Too many and it becomes a bother for the user, design time spent alphabetizing and showing only a portions of the alphabet at a time might make the users life a lot easier.