Expanding A Range As data Is Entered

W

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

[email protected]

end
 
R

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

[email protected]

end
 
K

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
 
Top