Example:

A1:A10 each contains a formula
A1:A8 contains the result of the formula
A9:A10 contains only the formula but no value

If a sort is carried out on A1:A10 this will result in A1:A2 displaying
empty cells
and A3:A10 displaying the sorted recorded. The reason for this is that any
cell containing no value but only the formula the formula will be displayed
first at the top of the sorted list.

Anyone know if there is code which could be included into a custom sort
command button which takes this into account and prevents the formula form
displaying at the top of the sorted range displaying empty cells.

Thanks for any help with this.

Pat

One approach might be to use a helper column with different formulas:

=if(a1="","zzzzzz",a1)
(dragged down)

Then sort by that column. (choose a nice string so that it sorts last.)

You could even have your macro add a column, add the formula, do the sort, and
remove the column.



Pat wrote:

Example:

A1:A10 each contains a formula
A1:A8 contains the result of the formula
A9:A10 contains only the formula but no value

If a sort is carried out on A1:A10 this will result in A1:A2 displaying
empty cells
and A3:A10 displaying the sorted recorded. The reason for this is that any
cell containing no value but only the formula the formula will be displayed
first at the top of the sorted list.

Anyone know if there is code which could be included into a custom sort
command button which takes this into account and prevents the formula form
displaying at the top of the sorted range displaying empty cells.

Thanks for any help with this.

Pat

--

Dave Peterson

Sort descending, then select just the cells with values and sort ascending.

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...

Example:

A1:A10 each contains a formula
A1:A8 contains the result of the formula
A9:A10 contains only the formula but no value

If a sort is carried out on A1:A10 this will result in A1:A2 displaying
empty cells
and A3:A10 displaying the sorted recorded. The reason for this is that
any
cell containing no value but only the formula the formula will be
displayed
first at the top of the sorted list.

Anyone know if there is code which could be included into a custom sort
command button which takes this into account and prevents the formula form
displaying at the top of the sorted range displaying empty cells.

Thanks for any help with this.

Pat

Or in your specific example, since the empty rows are already at the bottom,
just select A1:A8 and sort.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Sort descending, then select just the cells with values and sort
ascending.

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...

Example:

A1:A10 each contains a formula
A1:A8 contains the result of the formula
A9:A10 contains only the formula but no value

If a sort is carried out on A1:A10 this will result in A1:A2 displaying
empty cells
and A3:A10 displaying the sorted recorded. The reason for this is that
any
cell containing no value but only the formula the formula will be
displayed
first at the top of the sorted list.

Anyone know if there is code which could be included into a custom sort
command button which takes this into account and prevents the formula
form
displaying at the top of the sorted range displaying empty cells.

Thanks for any help with this.

Pat

Since posting this question earlier today I have looked at the problem again
and I have found the solution which was not obvious to me at the time.
The solution does not include sorting the column with the formula at all, it
is much more simple than that.

Thanks Tom for helping out anyway.

Best regards
Pat


"Tom Ogilvy" wrote in message
...
Or in your specific example, since the empty rows are already at the
bottom,
just select A1:A8 and sort.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Sort descending, then select just the cells with values and sort
ascending.

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...

Example:

A1:A10 each contains a formula
A1:A8 contains the result of the formula
A9:A10 contains only the formula but no value

If a sort is carried out on A1:A10 this will result in A1:A2
displaying
empty cells
and A3:A10 displaying the sorted recorded. The reason for this is
that
any
cell containing no value but only the formula the formula will be
displayed
first at the top of the sorted list.

Anyone know if there is code which could be included into a custom
sort
command button which takes this into account and prevents the formula
form
displaying at the top of the sorted range displaying empty cells.

Thanks for any help with this.

Pat

I guess you mean it literally displayed the formula

=SomeFormula

rather than appeared blank

=if(condition,"",formula)

Yes, getting it to evaluate the formula would fix that.

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
Since posting this question earlier today I have looked at the problem
again
and I have found the solution which was not obvious to me at the time.
The solution does not include sorting the column with the formula at all,
it
is much more simple than that.

Thanks Tom for helping out anyway.

Best regards
Pat


"Tom Ogilvy" wrote in message
...
Or in your specific example, since the empty rows are already at the
bottom,
just select A1:A8 and sort.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Sort descending, then select just the cells with values and sort
ascending.

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...

Example:

A1:A10 each contains a formula
A1:A8 contains the result of the formula
A9:A10 contains only the formula but no value

If a sort is carried out on A1:A10 this will result in A1:A2
displaying
empty cells
and A3:A10 displaying the sorted recorded. The reason for this is
that
any
cell containing no value but only the formula the formula will be
displayed
first at the top of the sorted list.

Anyone know if there is code which could be included into a custom
sort
command button which takes this into account and prevents the
formula
form
displaying at the top of the sorted range displaying empty cells.

Thanks for any help with this.

Pat