Expanding A Range As data Is Entered

[W. D. Allen Sr.]

I remember learning how to create a range that allowed entering new data
which in turn dynamically changed the range limits.

Can someone point me to an example with the relevant Excel functions?

Thanks,

W. D. Allen

(e-mail address removed)

end

 

[RagDyer]

Check out this site from Debra Dalgleish:

http://www.contextures.com/xlNames01.html#Dynamic
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I remember learning how to create a range that allowed entering new data
which in turn dynamically changed the range limits.

Can someone point me to an example with the relevant Excel functions?

Thanks,

W. D. Allen

(e-mail address removed)

end

 

[Ken Macksey]

Hi
If using a worksheet




' get the next row with no data in it
nextmtrow = Worksheets("sheet1").Range("b65536").End(xlUp).Offset(1, 0).Row

' resize the named range datarange
ActiveWorkbook.Names.Add Name:="Datarange", RefersToR1C1:="=Sheet1!" & "r" &
4 & "c" & 2 & ":" & "r" & nextmtrow - 1 & "c" & 6

if using a listbox on the worksheet

' the following code re dimensions the Listbox1 ListFillRange so
' no blank lines are shown in the Listbox
Worksheets("sheet1").ListBox1.ListFillRange = "b4:f8"
Worksheets("sheet1").ListBox1.ListFillRange = "DataRange"


if using Listbox on a a userform


' get the next row with no data in it
nextmtrow = Worksheets("sheet1").Range("b65536").End(xlUp).Offset(1, 0).Row

' resize the named range datarange
ActiveWorkbook.Names.Add Name:="Datarange", RefersToR1C1:="=Sheet1!" & "r" &
4 & "c" & 2 & ":" & "r" & nextmtrow - 1 & "c" & 6

' the following code re dimensions the Listbox1 rowsource so
' no blank lines are shown in the Listbox
UserForm1.ListBox1.RowSource = "B4:F10"
UserForm1.ListBox1.RowSource = "DataRange"


HTH

Ken