Add List value to hyperlink

[Emoncada]

I have the following code.

[VBA]ThisWorkbook.FollowHyperlink Address:="http://wwwapps.ups.com/WebTracking/t...=5.0&loc=en_US&" _
& "Requester=UPSHome&WBPM_lid=homepage%2Fct1.html_pnl_trk&trackNums=" _
& Me.ListBox1.List(0, 0) & "%0D%0A" _
& Me.ListBox1.List(1, 0) & "%0D%0A" _
& Me.ListBox1.List(2, 0) & "%0D%0A" _
& Me.ListBox1.List(3, 0) & "%0D%0A" _
& Me.ListBox1.List(4, 0) & "%0D%0A" _
& Me.ListBox1.List(5, 0) & "%0D%0A" _
& Me.ListBox1.List(6, 0) & "%0D%0A" _
& Me.ListBox1.List(7, 0) & "%0D%0A" _
& "&track.x=Track", NewWindow:=True[/VBA]

I have a listbox that gets populated from a spreadsheet.
This form will show the tracking #'s and with a click it checks all tracking numbers on UPS Website in group.
List box can have upto 25 rows, but lets say
Me.ListBox1.List(7, 0) = "" then it won't work.

How can I have it look at just the values in listbox and then have it add those to the hyperlink?

[mdmackillop]

If a blank entry in the listbox is the issue then join them in a string value first, correcting for the omission, and appending that string to the address.

[Emoncada]

I have the following that looks for blanks and removes them.
[VBA] For r = .ListCount - 1 To 0 Step -1
If .List(r, 0) = "" Then
.RemoveItem r
End If
Next r[/VBA]

The Problem I have is how do I get the correct information into the hyperlink?

[mdmackillop]

What text string is generated by your listbox values when a Blank is included?
if the Listcount changes, i thonk your code needs to be dynamic. Something like
[VBA]For i = 0 To ListBox1.ListCount - 1
txt = Me.ListBox1.List(i, 0) & "%0D%0A" _
Next
[/VBA]

[SamT]

[vba]For r = 0 To ListBox1.ListCount - 1
If .List(r, 0) <> "" Then
linkList = linkList & List(r, 0) & "%0D%0A"
End If
Next r
'If you KNOW the List box will never be competly empty, you can
'omit the If Right and End If lines
If Right(linkList, 6) = "%0D%0A" Then
linkList = Left(linkList, Len(linkList) - 6)
End If

EntireLink = EntireLink & linkList[/vba]

[snb]

or
[vba]
ThisWorkbook.FollowHyperlink Address:="http://wwwapps.ups.com/WebTracking/t...=5.0&loc=en_US&" _
& "Requester=UPSHome&WBPM_lid=homepage%2Fct1.html_pnl_trk&trackNums="_
& Replace(Replace(Join(Application.Transpose(ListBox1.List), "%0D%0A"), "%0D%0A%0D%0A", "%0D%0A"), "%0D%0A%0D%0A", "%0D%0A") & "&track.x=Track", NewWindow:=True

[/vba]