Transpose Row into Combobox

[f2e4]

Hi People

I have a row of dates that I need to transpose into a combobox

As this row is undefined in length (will vary as time goes on), i ca not put down a range.

i am using the following code....which works!! up to a point.


ActiveSheet.ComboBox9.Clear
Set subs = Worksheets("workload").Range("I3")
col = subs.CurrentRegion.End(xlToRight).Column
For x = 9 To col
ActiveSheet.ComboBox9.AddItem Worksheets("Workload").Cells(3, x).Value
Next

The only problem I have is that the above code picks up 'everything' from I3 to the right INCLUDING all the blanks after my data ends.

Does anyone know how to select only up to the last populated cell

Thanks

F

[JimmyTheHand]

Are you sure you need CurrentRegion?
Try simply
col = subs.End(xlToRight).Column

Jimmy

[Bob Phillips]

or maybe even



col = subs.CurrentRegion

[f2e4]

Are you sure you need CurrentRegion?
Try simply
col = subs.End(xlToRight).Column

Jimmy
This works perfectly

thanks very much :thumb

[JimmyTheHand]

Or maybe even

ActiveSheet.ComboBox9.Clear
Set subs = Worksheets("workload").Range("I3")
set subs = range(subs, subs.end(xltoRight))
ActiveSheet.ComboBox9.List = Application.Transpose(subs)
;)

[f2e4]

or maybe even



col = subs.CurrentRegion


Thanks for trying xld but i get a runtime error = '13' on this code

[f2e4]

Or maybe even

ActiveSheet.ComboBox9.Clear
Set subs = Worksheets("workload").Range("I3")
set subs = range(subs, subs.end(xltoRight))
ActiveSheet.ComboBox9.List = Application.Transpose(subs)
;)

Run-time error '1004':

method 'Range' of object '_worksheet' failed

the problem is in the 3rd line of code above



No need to try and solve this one as you solved my problem in your first reply.

Thanks for the enthusiasm though

[JimmyTheHand]

I'm not sure what, in this particular case, the problem may be, but the objective of my post was to show that you don't need to run a loop in order to fill the combobox. The essence is in line #4. :yes