CODEWITHSUNDEEP
vbaexcel
Clarus Dignus
Clarus Dignus

Reputation: 3897

Selecting a whole column as a range rather than a named range

What I have:

  1. I have drop-down list in my form that I'm populating from a worksheet.
  2. My code only works for named ranges.

What I need:

I need to populate my drop-down list from a whole column rather than a named range.

My VBA code:

Working code using a named range:

'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

What I've tried:

I've tried various permutations of the For Each line:

'For Each range_b In Worksheets("2.1 Clients").Columns(4)
'For Each range_b In Worksheets("2.1 Clients").Range(Columns(4))
'For Each range_b In Worksheets("2.1 Clients").Columns("D:D")

...that last of which seems to causing Excel to crash.

Upvotes: 0

Views: 693

Answers (2)

PatricK
PatricK

Reputation: 6433

It is more sensible to use Named Range instead of fixing it in code. Imagine someone added a column before D! You would have to change all codes that are hard coded. It is also better to put the With block on top of the For loop. You may also need to Clear the list before it.

Just make sure the named range ClientList refers to Worksheets("2.1 Clients").Range("D:D"). If you have done that, use below:

With Me.cs_ClientName1
    For Each range_b In ThisWorkbook.Names("ClientList").RefersToRange
        If Not IsEmpty(range_b) Then
            .AddItem range_b.Value
            .List(.ListCount - 1, 1) = range_b.Offset(0, 1).Value
        End If
    Next range_b
End With

A Better performance can be achieved if:

  1. List contains a header, with data starts right below it
  2. ClientList points to the Header, say D1 in Worksheets("2.1 Clients")

Code:

' 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

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

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

RANGE() is happy with a string address.

Edit#1:

Here is a working example:

Sub dural()
    Dim range_b As Range
    For Each range_b In Worksheets("2.1 Clients").Range("D:D")
        If range_b.Value = "good" Then
            MsgBox "good found"
            Exit Sub
        End If
    Next range_b
End Sub

Upvotes: 1

Related Questions