Populate drop-down list based on column

[Clarus Dignus]

I'm trying to modify the following working code:

'Populate drop-down list (combo box) from range...
Dim range_b As Range

For Each range_b In Worksheets("2.1 Clients").Range("ClientList")
  With Me.cs_ClientName1
    .AddItem range_b.Value
    .List(.ListCount - 1, 1) = range_b.Offset(0, 1).Value
  End With
Next range_b

End Sub

Instead of using a named range, I need to target a column. I've tried changing this line:

For Each range_b In Worksheets("2.1 Clients").Range("ClientList")

...to this:

For Each range_b In Worksheets("2.1 Clients").Column(4)

...but I'm receiving an error (object doesn't support this property/method).

Any pointers on how I should select the column?

 

[ZVI]

Try this:

Sub Populate()
  Const MyColumn = 4
  With Worksheets("2.1 Clients")
    Me.cs_ClientName1.List = .Range(.Cells(1, MyColumn), .Cells(.Rows.Count, MyColumn).End(xlUp)).Value
  End With
End Sub

 

[Clarus Dignus]

Thanks ZVI. I managed to get it working using the following code:

'Populate ClientName1 combo-box...

' Data Starts right below the Header (Named Range)
Set range_b = ThisWorkbook.Names("ClientList").RefersToRange.Offset(1, 0)
With Me.cs_ClientName1
    .Clear ' Clear existing data
    Do Until IsEmpty(range_b)
        .AddItem range_b.Value
        .List(.ListCount - 1, 1) = range_b.Offset(0, 1).Value
        Set range_b = range_b.Offset(1, 0)
    Loop
End With
Set range_b = Nothing

It requires that the header cell of the desired column be a named range (ClientList).

 

[ZVI]

Nice code, Clarus!
To speed it up you may also use this one:

Sub Populate1()
  With ThisWorkbook.Names("ClientList").RefersToRange
     Me.cs_ClientName1.List = .Worksheet.Range(.Offset(1, 1), .Cells(Rows.Count  - .Row, 1).End(xlUp)).Value
  End With
End Sub

Best Regards