Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a ListBox value with code | Excel Programming | |||
MultiColumn ListBox, linked to a range - select cells | Excel Programming | |||
Multi-select listbox help! | Excel Programming | |||
multi select listbox | Excel Programming | |||
Select from table and listbox | Excel Programming |