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

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

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

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

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

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

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

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