I am using a list box on a search form that can have 1 thru 4 columns from
the table on it, depending on the look up criteria.

The single retrieval works great, but two columns are giving me static.

The spreadsheet can contain between 1 and 5000 rows, so I defined a
2-dimensional array to hold the resutls. If the A cell of the row has a
value, put it and the value of column D of the same row in the array. When I
reach an A cell with no value, I've hit the end of the entries.

Then I load the array to the list box - so far so good. I see my results
just fine, but then there are a few thousand blank lines in the list box!

How do I limit what is transferred from the array to the list box? Here's
the code so far:

Dim ListData(1 To 5000, 1 To 500)
Dim sumName As String
Dim sumCity As String

For ListRow = 1 To 5000
If Sheet1.Range("A" & ListRow) > "" Then
sumCity = Sheet1.Range("C" & ListRow)
sumName = Sheet1.Range("A" & ListRow)
ListData(ListRow, 1) = sumCity
ListData(ListRow, 2) = sumName
Else
Exit For
End If
Next

lstDancer.TextAlign = fmTextAlignLeft
lstDancer.ColumnCount = 2
lstDancer.List = ListData

Hi Roy

one way of doing it would be to check for the last used row in column
a and use that value to set your range to check and Dim your array,
the code below should work for you


Dim ListRow As Integer
Dim LastRow As Integer
Dim ListData As Variant
Dim sumName As String
Dim sumCity As String

LastRow = [A65535].End(xlUp).Row

ReDim ListData(1 To LastRow, 1 To LastRow)

For ListRow = 1 To LastRow
If Sheet1.Range("A" & ListRow) > "" Then
sumCity = Sheet1.Range("C" & ListRow)
sumName = Sheet1.Range("A" & ListRow)
ListData(ListRow, 1) = sumCity
ListData(ListRow, 2) = sumName
Else
Exit For
End If
Next

lstDancer.TextAlign = fmTextAlignLeft
lstDancer.ColumnCount = 2
lstDancer.List = ListData

hope this helps

S

Absolutely perfect!! Thank you!!

"Incidental" wrote:

> Hi Roy
>
> one way of doing it would be to check for the last used row in column
> a and use that value to set your range to check and Dim your array,
> the code below should work for you
>
>
> Dim ListRow As Integer
> Dim LastRow As Integer
> Dim ListData As Variant
> Dim sumName As String
> Dim sumCity As String
>
> LastRow = [A65535].End(xlUp).Row
>
> ReDim ListData(1 To LastRow, 1 To LastRow)
>
> For ListRow = 1 To LastRow
> If Sheet1.Range("A" & ListRow) > "" Then
> sumCity = Sheet1.Range("C" & ListRow)
> sumName = Sheet1.Range("A" & ListRow)
> ListData(ListRow, 1) = sumCity
> ListData(ListRow, 2) = sumName
> Else
> Exit For
> End If
> Next
>
> lstDancer.TextAlign = fmTextAlignLeft
> lstDancer.ColumnCount = 2
> lstDancer.List = ListData
>
> hope this helps
>
> S
>
>
>