Adding Items to a ListBox-Eliminating Blanks-Part II

[jpendegraft]

Tom solved one issue...using a variable in the array...but I still nee
to eliminate the blank lines that this generates...

I want it to ONLY add lines the satisfy the conditions of the I
statement. currently it will add a line with NO DATA if the statemen
is false..

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 Su

 

[Bob Phillips]

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)

j=1
On Error Resume Next
For i = 1 To Packcount
If Sheet10.Cells(i, 1) = cbstate.Value Then
data(j, 1) = PackagesAvailable.Cells(i, 2).Value
data(j, 2) = PackagesAvailable.Cells(i, 3).Value
j=j+1
End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = data

End Sub




--

HTH

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

 

[Tom Ogilvy]

See answer provided in original thread.

 

[jpendegraft]

Thanks Bob!! That did the trick

 

[Tom Ogilvy]

Sure all the blanks aren't at the bottom of the dropdown? Perhaps that is
acceptable.