Adding Items to ListBox-eliminating Blanks

[jpendegraft]

Will post again as I do not see my post.

The Situation: I need to add items (mulitcolumn) to a listbox based o
the value of another combobox. Basically, the user will select th
state, and then based on the state--the listbox will populate.

The problem is 2 fold- first-I can get the items to add, but it als
adds blank lines. I do not want the blank lines-It basically adds
blank line if the value does not match or adds the data if it does.

The second part is that I have to use a constant ("66") rather than
variable ("PackCount") due to the way I structured the code. I'd lik
to use the variable of PACKCOUNT rather than 66 to determine the numbe
of loops for flexability.

Any help would be greatly appreciated.

My code is as follows:

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)

 

[eklarsen]

Tom's answer is close but the
"Packcount =" will generate a Syntax error and not the answer jp i
looking for

 

[jpendegraft]

Tom's solution (as usual) worked to get the PACKCOUNT set as a variabl
(by REDIMMING IT), but did not address the adding the blanks... An
help on the blanks?

Bob certainly would love a copy of th
(e-mail address removed)

 

[Tom Ogilvy]

You mean because
Packcount =
Application.WorksheetFunction.CountA(PackagesAvailable.Range("A:A"))

should all be on one line.

or

Packcount = _
Application.WorksheetFunction.CountA(PackagesAvailable.Range("A:A"))


Since I didn't change that line of code, and it appeared in two lines
(because of word wrap) in the OP's posting, I didn't go through and reformat
the posting - I assumed (maybe too much) that the OP could figure it out.

 

[Bob Phillips]

It's in the post.

--

HTH

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

 

[Tom Ogilvy]

I assumed that not using a fixed size array would eliminate the blanks. If
you still have blanks embedded in the array the obvious answer is not to add
them to the array, but that can be complex.

Sub PopulateBox()

Dim data()
Dim i As Long
Dim j as Long
Dim rng as Range

Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")

With PackagesAvailable
Set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With

' figure out how many values you will have
packcount = 0
for i = 1 to rng.rows.count
if sheet10.Cells(i,1).Value = cbState.Value and _
rng(i) <> "" then
packcount = packcount + 1
end if
Next
ListBox1.Clear

reDim Data(1 To Packcount, 1 To 2)


j = 0
For i = 1 To rng.rows.count
If Sheet10.Cells(i, 1) = cbState.Value Then
if PackagesAvailable.Cells(i,2) <> "" then
j = j + 1
Data(j, 1) = PackagesAvailable.Cells(i, 2).Value
Data(j, 2) = PackagesAvailable.Cells(i, 3).Value
End if
End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = Data

End Sub

code is untested, so it might have problems, but suggests a general
approach.

 

[jpendegraft]

Thanks Tom. Big help once again

 

[Tom Ogilvy]

apparently not.

 

[doughboy]

Re: Adding Items to ListBox-eliminating Blanks

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


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

Hi,
I am new at this or this forum for that matter...I would like to get
copy of this workbook
Regards
Raou