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]

Because the first value you are adding to the array is on row2 of the sheet, so that's -1 and then you are using a 0 based array instead of a 1 based array, so that is also -1

 

[DanteAmor]

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.

Hi, It is correct, less a line because the arrangement starts at 0 and less a line, because the data starts in row 2, I guess in row 1 you have the headings.

 

[Fluff]

However rather than looping through the rows you you could this to populate the combo.

   Me.ComboBox1.List = Application.Index(Ws.Range("A2:AA" & lastrow).Value, Evaluate("row(2:" & lastrow & ")"), Array(1, 2, 27))

 

[DanteAmor]

erased

 

[DanteAmor]

Then I found out that you need column 27.
It may be like this, only that the third column will be in column 27:

Sub UserForm_Initialize()
  Dim ws As Worksheet, LastRow As Long, a, r As Range, u As Range
  Set ws = Sheets("DataSheet")
  LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
  With Me.ComboBox1
    .SetFocus
    .Clear
    .ColumnHeads = False
[COLOR=#0000ff]    .ColumnCount = 27[/COLOR]
[COLOR=#0000ff]    .ColumnWidths = "240;240;" & WorksheetFunction.Rept("0;", 24) & "240"[/COLOR]
    .Font.Size = 14
[COLOR=#0000ff]    .List = ws.Range("A2:AA" & LastRow).Value[/COLOR]
  End With
End Sub

 

[TAPS_MikeDion]

Awesome! Thanks you both, not only for the replies, but for the code to try as well. You guys are the best.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Fluff]

You're welcome & thanks for the feedback

 

[TAPS_MikeDion]

Hi Fluff,

I just want to state that I'm a novice with VBA. I tried your code first since it shortened my code a lot, but I had to change "row(2:" to "row(1:" in order for it to get the first row of data. However, no matter what I've tried using your code, I can't get rid of the blank line at the bottom of the list in the ComboBox.

Thanks again,
Mike