Adding Items to a LISTBOX--eliminating Blank Lines
 

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(PackagesAvail able.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

--
Message posted from http://www.ExcelForum.com

Adding Items to a LISTBOX--eliminating Blank Lines
 

Sub PopulateBox()

Dim data()

Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount =
Application.WorksheetFunction.CountA(PackagesAvail able.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

--
Regards,
Tom Ogilvy


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

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

Any help would be great. Below is my code:

Sub PopulateBox()


Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount =
Application.WorksheetFunction.CountA(PackagesAvail able.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 Sub


---
Message posted from http://www.ExcelForum.com/

Adding Items to a LISTBOX--eliminating Blank Lines
 

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)

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

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

Any help would be great. Below is my code:

Sub PopulateBox()


Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount =
Application.WorksheetFunction.CountA(PackagesAvail able.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 Sub


---
Message posted from http://www.ExcelForum.com/