I am unclear how to use your approach, is this to be used in a userform. I
have little experience with userforms.

Pat
"Ken Macksey" wrote in message
...

Hi

Get the last used row in your range using something like the line below
starting with LastRow.
The "c65536" should be a column in your range that will always have data
in
it. Change the
c to the appropriate col letter.

LastRow = ActiveSheet.Range("c65536").End(xlUp).Offset(0, 0).Row



Assuming LastRow above returns row 30 , the code below re-defines
DataRange
to be row 5 col B to row 30 column i
or B5:I30

' resize the named range datarange
ActiveWorkbook.Names.Add Name:="Datarange", RefersToR1C1:="=BalanceSheet!"
&
"r" & 5 & "c" & 2 & ":" & "r" & LastRow & "c" & 9

If you are showing the data on a userform in a listbox or combobox, set
its
Rowsource property to DataRange
and then use the code below to show only the data with no blank lines at
the
bottom in the listbox.

' the following code re dimensions the Listbox1 rowsource so
' no blank lines are shown in the Listbox
sstring = Mainform.ListBox1.RowSource
Mainform.ListBox1.RowSource = sstring


HTH


Ken