Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Dim Arrays & List Box
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Dim Arrays & List Box
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Dim Arrays & List Box
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regional settings independent list separator in arrays | Excel Worksheet Functions | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
Help in generating Permuation&Sequencing list from two arrays | Excel Programming | |||
List Arrays of a Certain Size | Excel Programming | |||
List boxes, Arrays and Sorting | Excel Programming |