Listbox Select Last Value in range
 

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

Listbox Select Last Value in range
 

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 wrote:

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

Listbox Select Last Value in range
 

Thank you Dave.

"Dave Peterson" wrote in message
...
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 wrote:

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

Listbox Select Last Value in range
 

You're welcome.

Corey wrote:

Thank you Dave.

"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson