Hi,
running the following macro:
ActiveSheet.Unprotect
Range("B10:GJ89").Select
Selection.Sort Key1:=Range("BE10"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("BE1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWindow.ScrollColumn = 38
End Sub

This macro is applied to sort collumn BE which has the following formula for
each cell:
=IF(COUNT(DA10,BY10)=2,DA10-BY10,"")

When activated, it puts all the blank cells at the top of the collumn before
all the cells with values in. (NB it has sorted these correctly though in
descending order)

Any ideas what`s wrong folks?
Thanks for your time.
RR1

the cells contain "" which is a null string, not blank, and sorting
descending puts them first. Perhaps change the formula to return 0 and hide
zeros
either by formatting (General;General;)
or by not displaying the zero values -- option in Tools./Options/View or
equivalent in 2007 using Office Button.

"Romileyrunner1" wrote in message
...
Hi,
running the following macro:
ActiveSheet.Unprotect
Range("B10:GJ89").Select
Selection.Sort Key1:=Range("BE10"), Order1:=xlDescending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("BE1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveWindow.ScrollColumn = 38
End Sub

This macro is applied to sort collumn BE which has the following formula
for
each cell:
=IF(COUNT(DA10,BY10)=2,DA10-BY10,"")

When activated, it puts all the blank cells at the top of the collumn
before
all the cells with values in. (NB it has sorted these correctly though in
descending order)

Any ideas what`s wrong folks?
Thanks for your time.
RR1

Cheers Bob that makes sense.
As I have done before, If I use -999 as a default value (I have some
smallish negative #`s as values in the collumn), how do I phrase an average
of that column.

e.g. I want to say, "find the average of the cells in collumn BE that are
bigger than -900)
Any ideas Bob?
Thanks
RR1


"Bob Umlas" wrote:

the cells contain "" which is a null string, not blank, and sorting
descending puts them first. Perhaps change the formula to return 0 and hide
zeros
either by formatting (General;General;)
or by not displaying the zero values -- option in Tools./Options/View or
equivalent in 2007 using Office Button.

"Romileyrunner1" wrote in message
...
Hi,
running the following macro:
ActiveSheet.Unprotect
Range("B10:GJ89").Select
Selection.Sort Key1:=Range("BE10"), Order1:=xlDescending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("BE1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveWindow.ScrollColumn = 38
End Sub

This macro is applied to sort collumn BE which has the following formula
for
each cell:
=IF(COUNT(DA10,BY10)=2,DA10-BY10,"")

When activated, it puts all the blank cells at the top of the collumn
before
all the cells with values in. (NB it has sorted these correctly though in
descending order)

Any ideas what`s wrong folks?
Thanks for your time.
RR1

You can use an array* formula, like this:

=AVERAGE(IF(BE1:BE100-900,BE1:BE100))

Adjust the ranges to suit your data.

* An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <enter. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
edit the formula you will need to use CSE again.

Hope this helps.

Pete

On Sep 30, 8:13*pm, Romileyrunner1
wrote:
Cheers Bob that makes sense.
As I have done before, If I use -999 as a default value (I have some
smallish negative #`s as values in the collumn), how do I phrase an average
of that column.

e.g. I want to say, "find the average of the cells in collumn BE that are
bigger than -900)
Any ideas Bob?
Thanks
RR1



"Bob Umlas" wrote:
the cells contain "" which is a null string, not blank, and sorting
descending puts them first. Perhaps change the formula to return 0 and hide
zeros
either by formatting (General;General;)
or by not displaying the zero values -- option in Tools./Options/View or
equivalent in 2007 using Office Button.

"Romileyrunner1" wrote in message
...
Hi,
running the following macro:
ActiveSheet.Unprotect
* *Range("B10:GJ89").Select
* *Selection.Sort Key1:=Range("BE10"), Order1:=xlDescending, Header:=xlNo,
_
* * * *OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
* * * *DataOption1:=xlSortNormal
* * * *Range("BE1").Select
* *ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
* *ActiveWindow.ScrollColumn = 38
End Sub

This macro is applied to sort collumn BE which has the following formula
for
each cell:
=IF(COUNT(DA10,BY10)=2,DA10-BY10,"")

When activated, it puts all the blank cells at the top of the collumn
before
all the cells with values in. (NB it has sorted these correctly though in
descending order)

Any ideas what`s wrong folks?
Thanks for your time.
RR1- Hide quoted text -

- Show quoted text -

Cheers Pete: works a treat.
Many thanks mate.
RR1

"Pete_UK" wrote:

You can use an array* formula, like this:

=AVERAGE(IF(BE1:BE100-900,BE1:BE100))

Adjust the ranges to suit your data.

* An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <enter. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
edit the formula you will need to use CSE again.

Hope this helps.

Pete

On Sep 30, 8:13 pm, Romileyrunner1
wrote:
Cheers Bob that makes sense.
As I have done before, If I use -999 as a default value (I have some
smallish negative #`s as values in the collumn), how do I phrase an average
of that column.

e.g. I want to say, "find the average of the cells in collumn BE that are
bigger than -900)
Any ideas Bob?
Thanks
RR1



"Bob Umlas" wrote:
the cells contain "" which is a null string, not blank, and sorting
descending puts them first. Perhaps change the formula to return 0 and hide
zeros
either by formatting (General;General;)
or by not displaying the zero values -- option in Tools./Options/View or
equivalent in 2007 using Office Button.

"Romileyrunner1" wrote in message
...
Hi,
running the following macro:
ActiveSheet.Unprotect
Range("B10:GJ89").Select
Selection.Sort Key1:=Range("BE10"), Order1:=xlDescending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("BE1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveWindow.ScrollColumn = 38
End Sub

This macro is applied to sort collumn BE which has the following formula
for
each cell:
=IF(COUNT(DA10,BY10)=2,DA10-BY10,"")

When activated, it puts all the blank cells at the top of the collumn
before
all the cells with values in. (NB it has sorted these correctly though in
descending order)

Any ideas what`s wrong folks?
Thanks for your time.
RR1- Hide quoted text -

- Show quoted text -

You're welcome - thanks for feeding back.

Is that Romiley near Stockport?

Pete

On Oct 1, 6:47*am, Romileyrunner1
wrote:
Cheers Pete: works a treat.
Many thanks mate.
RR1



"Pete_UK" wrote:
You can use an array* formula, like this:

=AVERAGE(IF(BE1:BE100-900,BE1:BE100))

Adjust the ranges to suit your data.

* An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <enter. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
edit the formula you will need to use CSE again.

Hope this helps.

Pete

On Sep 30, 8:13 pm, Romileyrunner1
wrote:
Cheers Bob that makes sense.
As I have done before, If I use -999 as a default value (I have some
smallish negative #`s as values in the collumn), how do I phrase an average
of that column.

e.g. I want to say, "find the average of the cells in collumn BE that are
bigger than -900)
Any ideas Bob?
Thanks
RR1

"Bob Umlas" wrote:
the cells contain "" which is a null string, not blank, and sorting
descending puts them first. Perhaps change the formula to return 0 and hide
zeros
either by formatting (General;General;)
or by not displaying the zero values -- option in Tools./Options/View or
equivalent in 2007 using Office Button.

"Romileyrunner1" wrote in message
...
Hi,
running the following macro:
ActiveSheet.Unprotect
* *Range("B10:GJ89").Select
* *Selection.Sort Key1:=Range("BE10"), Order1:=xlDescending, Header:=xlNo,
_
* * * *OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
* * * *DataOption1:=xlSortNormal
* * * *Range("BE1").Select
* *ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
* *ActiveWindow.ScrollColumn = 38
End Sub

This macro is applied to sort collumn BE which has the following formula
for
each cell:
=IF(COUNT(DA10,BY10)=2,DA10-BY10,"")

When activated, it puts all the blank cells at the top of the collumn
before
all the cells with values in. (NB it has sorted these correctly though in
descending order)

Any ideas what`s wrong folks?
Thanks for your time.
RR1- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -