View Single Post
  #8   Report Post  
Pat
 
Posts: n/a
Default

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