Sorting issue

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

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.
 
Or in your specific example, since the empty rows are already at the bottom,
just select A1:A8 and sort.
 
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
 
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.
 
Back
Top