delete blank rows and remove blanks from dropdown

[stewart]

I have a range of names and number (a4:b105) Column a contains names
and column b contains numbers. if the number is not assigned to a name
the row is hidden. I am devoloping a userform that automates adding/
deleting names. to delete the user selects a number from a dropdown
and it clears cells and hides the row. I want the user to only see
current numbers(not hidden ones). I have code that accomplishes that
but it is a hypothetical stew of code that i have pieced together. it
works but i think there must be some thing more efficient. Any
suggestions?

Private Sub UserForm_Activate()
Application.ScreenUpdating = False
Sheets("Tracker").Select
Range("A5:B104").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Range("a105").Select

For i = 104 To 1 Step -1

If Range("a" & i).Value = "" Then
Rows(i).EntireRow.Select
Selection.Delete
End If

Next i

Dim rng As Range
Dim rng2 As Range
Dim cell As Range

Set rng = Range("B1:B105").SpecialCells(xlCellTypeConstants)
On Error Resume Next
Set rng2 = Range("B1:B105").SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rng2 Is Nothing Then
Set rng = Union(rng, rng2)
End If
For Each cell In rng
Me.cboDeleteNumber.AddItem cell.Value
Next cell

Sheets("tracker").Activate
Application.ScreenUpdating = True

End Sub

i also run this code every time a number is deleted

 

[Tom Ogilvy]

Private Sub UserForm_Activate()
Application.ScreenUpdating = False
Set rng = Sheets("Tracker").Range("A5:B104")
v = rng.Columns(2).Value
v1 = rng.Columns(1).Value
For i = 1 To UBound(v)
If Len(Trim(v(i, 1))) > 0 And _
Len(Trim(v1(i, 1))) > 0 Then
Me.cboDeleteNumber.AddItem Trim(v(i, 1))
End If
Next i
Sheets("tracker").Activate
Application.ScreenUpdating = True

End Sub

worked for me.