ComboBox list ends with 2 blank rows of data ???

[TAPS_MikeDion]

Hi all,

I'm sure I'm missing something small, but does anyone know why I have to use "LastRow-2" in the code below to keep from ending up with 2 empty rows of data at the bottom of my ComboBox list?

I guess I would get it if I had to use LastRow-1 since the array might be putting in an extra line, because the array starts at 0. I'm just confused as to why I have to use -2.

Thanks in advance,
Mike

Sub UserForm_Initialize()

  Dim ws As Worksheet
  Dim i As Long
  Dim LastRow As Long
  
  Set ws = Sheets("DataSheet")

  LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
  
  Dim MyArray()
  ReDim MyArray(LastRow-2, 2) As Variant
  
  With Me.ComboBox1
    .SetFocus
    .Clear
    .ColumnHeads = False
    .ColumnCount = 3
    .ColumnWidths = "240;240;240"
    .Font.Size = 14
    For i = 0 To LastRow-2
      MyArray(i, 0) = ws.Cells(i + 2, 1)
      MyArray(i, 1) = ws.Cells(i + 2, 2)
      MyArray(i, 2) = ws.Cells(i + 2, 27)
    Next i
    .List = MyArray
  End With
  
End Sub

 

[Fluff]

You're quite right, it should be

      Me.ComboBox1.List = Application.Index(ws.Range("[COLOR=#ff0000]A1[/COLOR]:AA" & LastRow).Value, Evaluate("row(2:" & LastRow & ")"), Array(1, 2, 27))