Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Pat
 
Posts: n/a
Default Defined range difficulty

The defined range below extends the range beyond the cells where the data
ends.
The start of the range is Centre!$C$77 the end of the range is C1054. The
last cell containing data is C807. The data in C is the result of a formula
and is in the range C77:C1000

=OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1)

Anyone familiar with all of this?
Thanks if you can help.
Pat



  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

You obviously have data in C1:C77 thus it will be counted, you can use

=OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1))


Regards


Peo Sjoblom


"Pat" wrote:

The defined range below extends the range beyond the cells where the data
ends.
The start of the range is Centre!$C$77 the end of the range is C1054. The
last cell containing data is C807. The data in C is the result of a formula
and is in the range C77:C1000

=OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1)

Anyone familiar with all of this?
Thanks if you can help.
Pat




  #3   Report Post  
doco
 
Posts: n/a
Default

Quote:
Anyone familiar with all of this?
Yes.

Beyond that I am not sure what your question is...


"Pat" wrote in message
...
The defined range below extends the range beyond the cells where the data
ends.
The start of the range is Centre!$C$77 the end of the range is C1054. The
last cell containing data is C807. The data in C is the result of a
formula
and is in the range C77:C1000

=OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1)

Anyone familiar with all of this?
Thanks if you can help.
Pat





  #4   Report Post  
Pat
 
Posts: n/a
Default

You obviously have data in C1:C77 thus it will be counted, you can use
Yes you are correct.

This has now eliminated many empty cells. I say many because where there is
an empty cell with a formula the range includes these. The data finishes at
C807 but the formula continues on to C1000. Can the defined range be made
to ignore a formula range on a sheet?

Pat


"Peo Sjoblom" wrote in message
...
You obviously have data in C1:C77 thus it will be counted, you can use

=OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1))


Regards


Peo Sjoblom


"Pat" wrote:

The defined range below extends the range beyond the cells where the data
ends.
The start of the range is Centre!$C$77 the end of the range is C1054.
The
last cell containing data is C807. The data in C is the result of a
formula
and is in the range C77:C1000

=OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1)

Anyone familiar with all of this?
Thanks if you can help.
Pat






  #5   Report Post  
Ken Macksey
 
Posts: n/a
Default


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




  #6   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

You can use this instead of counta

SUMPRODUCT(--(C77:C65536<""))


Regards,


Peo Sjoblom

"Pat" wrote:

You obviously have data in C1:C77 thus it will be counted, you can use

Yes you are correct.

This has now eliminated many empty cells. I say many because where there is
an empty cell with a formula the range includes these. The data finishes at
C807 but the formula continues on to C1000. Can the defined range be made
to ignore a formula range on a sheet?

Pat


"Peo Sjoblom" wrote in message
...
You obviously have data in C1:C77 thus it will be counted, you can use

=OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1))


Regards


Peo Sjoblom


"Pat" wrote:

The defined range below extends the range beyond the cells where the data
ends.
The start of the range is Centre!$C$77 the end of the range is C1054.
The
last cell containing data is C807. The data in C is the result of a
formula
and is in the range C77:C1000

=OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1)

Anyone familiar with all of this?
Thanks if you can help.
Pat







  #7   Report Post  
Pat
 
Posts: n/a
Default

Did you mean to enter it like this:
=OFFSET(Centre!$C$77,0,0,SUMPRODUCT(--(C77:C65536<""))

or to enter like this:
=OFFSET(Centre!$C$77,0,0,SUMPRODUCT(Centre!(C77:C6 5536<""))

Either way I have not been successful.

Pat



"Peo Sjoblom" wrote in message
...
You can use this instead of counta

SUMPRODUCT(--(C77:C65536<""))


Regards,


Peo Sjoblom

"Pat" wrote:

You obviously have data in C1:C77 thus it will be counted, you can use

Yes you are correct.

This has now eliminated many empty cells. I say many because where there

is
an empty cell with a formula the range includes these. The data finishes

at
C807 but the formula continues on to C1000. Can the defined range be

made
to ignore a formula range on a sheet?

Pat


"Peo Sjoblom" wrote in message
...
You obviously have data in C1:C77 thus it will be counted, you can use

=OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1))


Regards


Peo Sjoblom


"Pat" wrote:

The defined range below extends the range beyond the cells where the

data
ends.
The start of the range is Centre!$C$77 the end of the range is C1054.
The
last cell containing data is C807. The data in C is the result of a
formula
and is in the range C77:C1000

=OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1)

Anyone familiar with all of this?
Thanks if you can help.
Pat









  #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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing a range name as an argument to the Index Function Michael Sharpe Excel Discussion (Misc queries) 3 September 5th 12 01:33 PM
Construct a range in VB Steve Excel Discussion (Misc queries) 3 December 29th 04 03:01 PM
How do I sum a range after 2 different conditions are met (2 colu. Holly B. Excel Discussion (Misc queries) 3 December 21st 04 04:47 PM
named range refers to: in a chart Spencer Hutton Excel Discussion (Misc queries) 1 December 14th 04 11:15 PM
HTML_Control Range name Steven Cheng Excel Discussion (Misc queries) 4 December 10th 04 11:12 PM


All times are GMT +1. The time now is 03:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"