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.
--
Regards,
Tom Ogilvy
"stewart" wrote:
> 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
>
>
|
Similar Threads
