Add dynamic range from a excel row to a listbox

[davedave24]

Hi

I need to add a dynamically sized range from a row in excel into a listbox (column 1).

The range will always start at G1, and end at whatever the last used cell is in row 1.

I can do this fine when adding a column using this method:

    Dim rngC As Range
    With Sheets("Customers")
        Set rngC = .Range(.Range("A2"), .Cells(Rows.Count, 1).End(xlUp))
    End With
    
    comboCustomer.RowSource = rngC.Address(External:=True)

However it doesn't work for the Row. I've tried to modify it to this:

    Set rngC = .Range(.Range("G1"), .Cells(1, Columns.Count).End(xlToRight))

But with that, it only picks up the first entry (cell G1)

 

[PHV]

Perhaps this ?
Set rngC = .Range(.Range("G1"), .Cells(1, Columns.Count).End(xlTo[!]Left[/!]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

hi,

Just assign the Range Name. Either use an OFFSET() formula or a sheet change event to make the name dynamic.

If you have Excel 2007+, just make your list a Structured Table (Insert > Tables > Table) and use Formulas > Define Names > Create from selection to create names in TOP row. Use the Range Name in the table heading. as you change the data in the table your Named Range will automatically and dynamically adjust!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}