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

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
619
Office Version
  1. 2011
Platform
  1. MacOS
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

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

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
However rather than looping through the rows you you could this to populate the combo.
Code:
   Me.ComboBox1.List = Application.Index(Ws.Range("A2:AA" & lastrow).Value, Evaluate("row(2:" & lastrow & ")"), Array(1, 2, 27))
 
Upvote 0
Then I found out that you need column 27.
It may be like this, only that the third column will be in column 27:

Code:
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
 
Upvote 0
Awesome! Thanks you both, not only for the replies, but for the code to try as well. You guys are the best.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top