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
|