Listbox Select Last Value in range

[Corey]

Is there a line i can ADD to the below code to select the LAST value(not empty) in the Range ?

Private Sub UserForm_Initialize()
Dim myCell As Range
Dim myRng As Range
Set myRng = Worksheets("RTW Plan").Range("N80:N99")
With ListBox1
For Each myCell In myRng.cells
If Trim(myCell.Value) = "" Then
'skip it
Else
.AddItem myCell.Value
End If
Next myCell
End With
End Sub

Corey....

 

[Dave Peterson]

As long as non-empty cells are skipped, you can just pick out the last one
added.

Private Sub UserForm_Initialize()
Dim myCell As Range
Dim myRng As Range
Set myRng = Worksheets("RTW Plan").Range("N80:N99")
With ListBox1
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'skip it
Else
.AddItem myCell.Value
End If
Next myCell
.ListIndex = .ListCount - 1
End With
End Sub

The count of the items in the list goes from 1 to N.
The first item in the list is 0. So it's indexed from 0 to N-1.

 

[Corey]

Thank you Dave.

As long as non-empty cells are skipped, you can just pick out the last one
added.

Private Sub UserForm_Initialize()
Dim myCell As Range
Dim myRng As Range
Set myRng = Worksheets("RTW Plan").Range("N80:N99")
With ListBox1
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'skip it
Else
.AddItem myCell.Value
End If
Next myCell
.ListIndex = .ListCount - 1
End With
End Sub

The count of the items in the list goes from 1 to N.
The first item in the list is 0. So it's indexed from 0 to N-1.

 

[Dave Peterson]

You're welcome.

Thank you Dave.

As long as non-empty cells are skipped, you can just pick out the last one
added.

Private Sub UserForm_Initialize()
Dim myCell As Range
Dim myRng As Range
Set myRng = Worksheets("RTW Plan").Range("N80:N99")
With ListBox1
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'skip it
Else
.AddItem myCell.Value
End If
Next myCell
.ListIndex = .ListCount - 1
End With
End Sub

The count of the items in the list goes from 1 to N.
The first item in the list is 0. So it's indexed from 0 to N-1.