Defined range difficulty

[Pat]

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

 

[Guest]

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

 

[doco]

Anyone familiar with all of this?

Yes.

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

 

[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

 

[Ken Macksey]

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

 

[Guest]

You can use this instead of counta

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


Regards,


Peo Sjoblom

 

[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:C65536<>""))

Either way I have not been successful.

Pat

 

[Pat]

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

Pat