Extra lines in Combobox

[Skyybot]

Can anyone tell me why there are two extra, blank lines at the end of my Combobox? (Sorry for the pic. I got an Overflow error with XL2BB)

Private Sub ComboBox1_DropButtonClick()
Dim cfName As String, clName As String, custList As Range
Dim lRow As Long, cell As Range, custData As Range
Dim i As Long

Me.Cells(1, 1).Select
lRow = Me.Rows(Me.Rows.Count).End(xlUp).Row
Set custList = Me.Range("A2:G" & lRow)

With Me.ComboBox1
    .ColumnCount = 2
    .ColumnHeads = False
    .ListFillRange = custList.Range(custList.Cells(1, 1), custList.Cells(lRow, 2)).Address
    .DropDown
End With

End Sub

 

[NoSparks]

default listrows on the combobox is 8
try

With Me.ComboBox1
    .ListRows = 5
    .ColumnCount = 2
    .ColumnHeads = False
    .ListFillRange = custList.Range(custList.Cells(1, 1), custList.Cells(lRow, 2)).Address
    .DropDown
End With

 

[dreid1011]

Can anyone tell me why there are two extra, blank lines at the end of my Combobox? (Sorry for the pic. I got an Overflow error with XL2BB)
View attachment 99174

Private Sub ComboBox1_DropButtonClick()
Dim cfName As String, clName As String, custList As Range
Dim lRow As Long, cell As Range, custData As Range
Dim i As Long

Me.Cells(1, 1).Select
lRow = Me.Rows(Me.Rows.Count).End(xlUp).Row
Set custList = Me.Range("A2:G" & lRow)

With Me.ComboBox1
    .ColumnCount = 2
    .ColumnHeads = False
    .ListFillRange = custList.Range(custList.Cells(1, 1), custList.Cells(lRow, 2)).Address
    .DropDown
End With

End Sub

Upon testing, this line is picking a range that ends two rows below the last used cell.

.ListFillRange = custList.Range(custList.Cells(1, 1), custList.Cells(lRow, 2)).Address

 

[dreid1011]

Upon testing, this line is picking a range that ends two rows below the last used cell.

.ListFillRange = custList.Range(custList.Cells(1, 1), custList.Cells(lRow, 2)).Address

This ended up working for me... I don't understand why the range was offset so much.

.ListFillRange = custList.Range(custList.Cells(0, 1), custList.Cells(lRow - 2, 2)).Address

However, you are starting in row 3, so maybe this:

.ListFillRange = custList.Range(custList.Cells(1, 1), custList.Cells(lRow - 2, 2)).Address

 

[Skyybot]

Careful, there is a hidden Row. I couldn't get a list without First Name Last Name at the top (no Column Headers displayed Column1 , Column2). I was trying to get a list without headers, or any other values that aren't in the list.

 

[dreid1011]

Careful, there is a hidden Row. I couldn't get a list without First Name Last Name at the top (no Column Headers displayed Column1 , Column2). I was trying to get a list without headers, or any other values that aren't in the list.

I think if you use the line I suggested with the first cell reference being Cells(0,1) you can use row 2 properly.

 

[Skyybot]

This ended up working for me... I don't understand why the range was offset so much.

.ListFillRange = custList.Range(custList.Cells(0, 1), custList.Cells(lRow - 2, 2)).Address

However, you are starting in row 3, so maybe this:

.ListFillRange = custList.Range(custList.Cells(1, 1), custList.Cells(lRow - 2, 2)).Address

I just caught that. I don't know why the Range was Offset so much either. I took clues from @dreid1011 and @NoSparks and got it.

Private Sub ComboBox1_DropButtonClick()
Dim cfName As String, clName As String, custList As Range
Dim lRow As Long, cell As Range, custData As Range
Dim i As Long

Me.Cells(1, 1).Select
lRow = Me.Rows(Me.Rows.Count).End(xlUp).Row
Set custList = Me.Range("A2:G" & lRow)

With Me.ComboBox1
    .ListRows = custList.Rows.Count
    .ColumnCount = 2
    .ColumnHeads = False
    .ListFillRange = custList.Range(custList.Cells(1, 1), custList.Cells(lRow - 2, 2)).Address
    .DropDown
End With

End Sub