Populate number in combo box

[Av8tordude]

I have this code that populates ten years before and 10 years after the current year, however I have various dates starting from column D15 that I would like to only show the range of years in the column. For example, If the first date is 03/02/2013 and the last date is 12/01/2018, I would like to only show the years 2013 - 2018. Can someone assist with my request.

For i = -10 To 10    
    If i = 1 Then cbYr.AddItem Format((Date), "yyyy") _
        Else: cbYr.AddItem Format((DateAdd("yyyy", (i - 1), Date)), "yyyy")
Next

Thank you kindly

 

[Akuini]

Maybe this:

Set Rng = Range("D15", Cells(Rows.Count, "D").End(xlUp))

a = Year(Application.WorksheetFunction.Min(Rng))
b = Year(Application.WorksheetFunction.Max(Rng))

For i = a To b
ComboBox1.AddItem i
Next

 

[Av8tordude]

Works perfect!!! Is there a way for the combo box to show the current year. I've tried the code below, but it using the year that happens to be in that index number.

cbYr.ListIndex = 1

Thank you again for your help!

 

[Akuini]

Works perfect!!! Is there a way for the combo box to show the current year. I've tried the code below, but it using the year that happens to be in that index number.

Thank you again for your help!

Try this:
(Note: in my first code you need to add: ComboBox1.Clear 'needs to be cleared first)

Set Rng = Range("D15", Cells(Rows.Count, "D").End(xlUp))

a = Year(Application.WorksheetFunction.Min(Rng))
b = Year(Application.WorksheetFunction.Max(Rng))

ComboBox1.Clear 'needs to be cleared first
For i = a To b
ComboBox1.AddItem i
Next

For i = 0 To ComboBox1.ListCount - 1
    If ComboBox1.List(i) = CStr(Year(Date)) Then
        ComboBox1.ListIndex = i
        Exit For
    End If
Next i

 

[Av8tordude]

Thank you Akuini for your assistance. Code works great!

 

[Rick Rothstein]

Thank you Akuini for your assistance. Code works great!

I think you can do what you want with just these two lines of code...

[table="width: 500"]
[tr]
	[td]  ComboBox1.List = Evaluate("ROW(" & Year(Application.Min([D:D])) & ":" & Year(Application.Max([D:D])) & ")")
  ComboBox1.Value = Year(Now)
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

I think you can do what you want with just these two lines of code...

[table="width: 500"]
[tr]
	[td]  ComboBox1.List = Evaluate("ROW(" & Year(Application.Min([D:D])) & ":" & Year(Application.Max([D:D])) & ")")
  ComboBox1.Value = Year(Now)
End Sub[/td]
[/tr]
[/table]

Actually, we can streamline that code somewhat...

Private Sub UserForm_Click()
  ComboBox1.List = Evaluate("ROW(" & [YEAR(MIN(D:D))] & ":" & [YEAR(MAX(D:D))] & ")")
  ComboBox1.Value = Year(Now)
End Sub

 

[Av8tordude]

Hi Rick...Thank you. How can I have the code to start from D15 until the last row?

 

[Rick Rothstein]

Hi Rick...Thank you. How can I have the code to start from D15 until the last row?

Change the red numbers to a row number that is larger than the maximum row you would ever expect to have data in...

[table="width: 500"]
[tr]
	[td]  ComboBox1.List = Evaluate("ROW(" & [YEAR(MIN(D15:D[B][COLOR="#FF0000"]999[/COLOR][/B]))] & ":" & [YEAR(MAX(D15:D[B][COLOR="#FF0000"]999[/COLOR][/B]))] & ")")
  ComboBox1.Value = Year(Now)[/td]
[/tr]
[/table]