How to dynamically change range of Listbox?

Question

On sheet2 I've this

Private Sub Worksheet_Activate() 
  Sheet2.Listbox1.ListFillRange = Sheet1.Range("A2:A10").Address(, , , True) 
End Sub

When the range on Sheet1 changes, for example to A11, I want to change to Range("A2:A11") Because I know the position of rowx, I tried

Sheet2.Listbox1.ListFillRange = Sheet1.Range(cells(2, 1), cells(rowx, `1)).Address(, , , True)

But I didn't succeed. Why?

See [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) — , Sep 22 '17 at 14:27

score 1 · Answer 1 · answered Sep 22 '17 at 14:20

1

You didn't qualify the Cells with the Sheet1 reference.

Give this a try and see if that works for you...

Sheet2.Listbox1.ListFillRange = Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(rowx, 1)).Address(, , , True)

answered Sep 22 '17 at 14:20

1 Answers1