Using LastRow Code to Fill ComboBox.List But it Doesn't Work With Only 1 Row of Data

[RunTime91]

Greetings ~

The below code works great as long as there is more than one row of data to populate the List.

CmbCallRsn.List = Ws1.Range("A2:A" & Lr).Value

However to insure accurate results when there is only one row of data to populate I use the following and get a "Could Not Set The List Property. Invalid Property Array Index"

If Lr = 2 Then 
CmbCallRsn.List = Ws1.Range("A2").Value
Else
CmbCallRsn.List = Ws1.Range("A2:A" & Lr).Value
End If

Thank You for any assistance

RT91

 

[Cubist]

That error message indicates you're trying to access an index or 0 or negative. How are you getting the Lr?

 

[RunTime91]

Thanks Cubist for chiming in...

Here is the line of code I'm using for LastRow

Lr = Ws1.Cells(Rows.Count, 1).End(xlUp).Row

 

[Cubist]

Thanks. After you run the code, hover over Lr. What is the value of Lr?

 

[RunTime91]

The value is 2 ...

I have tried the following:

CmbCallRsn.List = Ws1.Range("A:A" & Lr).Value
CmbCallRsn.List = Ws1.Range("A:" & Lr).Value
CmbCallRsn.List = Ws1.Range("A1:A" & Lr).Value - (This doesn't error but predictably it pulls the header row into the ComboBox DropDown)
CmbCallRsn.List = Ws1.Range("A" & Lr).Value
et.al

 

[Cubist]

Try this.

If Lr = 2 Then 
CmbCallRsn.AddItem = Ws1.Range("A2").Value
Else
CmbCallRsn.List = Ws1.Range("A2:A" & Lr).Value
End If

 

[RunTime91]

Pulls a 'Compile Error' - Expected Function or Variable

 

[Cubist]

hm...Can you post your entire code?

 

[RunTime91]

Absolutely...Here you go...

Dim Ws1 As Worksheet
Set Ws1 As ThisWorkbook.Sheets("UndrPaidTxs")
Lr = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
If Lr = 2 Then 
CmbCallRsn.List = Ws1.Range("A2").Value
Else
CmbCallRsn.List = Ws1.Range("A2:A" & Lr).Value
End If
End Sub

 

[Cubist]

Try this. I think you need to use the Transpose function to convert the column of values to a row, since ComboBoxes expect a single-dimensional array.

Sub PopulateComboBox()
    Dim Ws1 As Worksheet
    Set Ws1 = ThisWorkbook.Sheets("UndrPaidTxs")
    
    Dim Lr As Long
    Lr = Ws1.Cells(Ws1.Rows.Count, 1).End(xlUp).Row
    
    If Lr = 2 Then
        CmbCallRsn.AddItem Ws1.Range("A2").Value
    Else
        CmbCallRsn.List = Application.WorksheetFunction.Transpose(Ws1.Range("A2:A" & Lr).Value)
    End If
End Sub