Listbox Select Last Value in range

C

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....
 
D

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.
 
C

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.
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top