For each or Named Range to populate ListBox

Question

All I am trying to populate a listbox with a For Each loop which iterates through the rows. The for each loop is going through the items in a Named range (ProgramIDs).

The current code I am using is

If Len(ProjectInformation.Range("H2").Value) = 7 Then

    Dim Lr As Long
    Lr = Range("H1048576").End(xlUp).Row
    For Each C In Range("H2:H" & Lr)
        With Program_ListBox
            .AddItem C.Value
        End With
    Next C

End If

I fear this is a very basic question however after researching the website / google I simply cannot get this simple task to function.

Any help would be appreciated.

Answer 1

Range("H2:H" & Lr) references the cells on the ActiveSheet. You should always fully qualify your references.

With ProjectInformation
    If Len(.Range("H2").Value) = 7 Then
        For Each C In .Range("H2", .Range("H" & .Rows.Count).End(xlUp))
            With Program_ListBox
                .AddItem C.Value
            End With
        Next C
    End If
End With

There is no need loop the cells to the add the values to the listbox. You can assign the Range().Value array directly to the Listbox.List array.

 With ProjectInformation
    If Len(.Range("H2").Value) = 7 Then
        Program_ListBox.List = .Range("H2", .Range("H" & .Rows.Count).End(xlUp)).Value
    End If
End With