Expanding A Range As data Is Entered

  • Thread starter Thread starter W. D. Allen Sr.
  • Start date Start date
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

(e-mail address removed)

end
 
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
 
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
 
Back
Top