Using dynamic ranges to populate combobox

[hungledink]

I have a combobox in a userform which takes its values form a dynamic range using this formula


=OFFSET($A$1,0,0,COUNT($A:$A),1)


This works ok but I want to add rows with new data at the start of this list, and include this new data in the combobox. When I do this though it corrupts the formula.


If I add one row in row 1:1 then the formula changes to say =OFFSET($A$2,0,0,COUNT($A:$A),1)


How do I get round this issue?


Thanks in advance.

 

[Domenic]

Try...

=OFFSET(INDEX($A:$A,1),0,0,COUNT($A:$A),1)

Hope this helps!

 

[AlphaFrog]

Alternatively, populate the Combobox like this...

 ComboBox1.RowSource = ""
 ComboBox1.List = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Rows.Count).End(xlUp)).Value

 

[hungledink]

Alternatively, populate the Combobox like this...

 ComboBox1.RowSource = ""
 ComboBox1.List = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Rows.Count).End(xlUp)).Value

Thanks, this works nicely.

This formula: =OFFSET(INDEX($A:$A,1),0,0,COUNT($A:$A),1) : just seemed to create a blank combobox but the correct number of blank lines in that combobox if that makes sense.

 

[Domenic]

This formula: =OFFSET(INDEX($A:$A,1),0,0,COUNT($A:$A),1) : just seemed to create a blank combobox but the correct number of blank lines in that combobox if that makes sense.

If you insert a number of rows and leave those cells blank, you'll get blank entries in the ComboBox. Is this the case?