Adding Items to a LISTBOX--eliminating Blank Lines

[jpendegraft]

The Situation: I am trying to loop through a range to fill a listbo
based on the value of another combobox (imagine selecting state an
having another box populate with only information specific to th
selected state). I need to add multiple columns of information to th
box if the "row qualifies". I can get my items to add, but also ge
blank lines where the row does not qualify. I need to eliminate th
blanks.

I also would like to use a VARIABLE instead of a constant for the # o
loops. Currently, the way it is set up is using an array whic
requires a constant ("66"). I have added a line to generate th
desired variable ("PackCount").

Any help would be great. Below is my code:

Sub PopulateBox()


Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount
Application.WorksheetFunction.CountA(PackagesAvailable.Range("A:A"))
Dim i As Integer

ListBox1.Clear

Dim Data(1 To 66, 1 To 2)

On Error Resume Next
For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 1) = PackagesAvailable.Cells(i, 2).Value
End If
Next i

For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 2) = PackagesAvailable.Cells(i, 3).Value

End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = Data

End Su

 

[Tom Ogilvy]

Sub PopulateBox()

Dim data()

Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount =
Application.WorksheetFunction.CountA(PackagesAvailable.Range("A:A"))
Dim i As Integer

ListBox1.Clear

reDim Data(1 To Packcount, 1 To 2)

On Error Resume Next
For i = 1 To Packcount
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 1) = PackagesAvailable.Cells(i, 2).Value
Data(i, 2) = PackagesAvailable.Cells(i, 3).Value
End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = Data

End Sub

 

[Bob Phillips]

I have a sample workbook that handles any number of dependent comboboxes.
Mail me if you want a copy.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)