Sorting Formula

[Don Guillett]

How about filtering out the 0's before or after the sort

 

[Salza]

Hi all ...

I am sorting a column using the formula below.

Selection.Sort Key1:=Range("B9"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Say the cells in the column, I want to sort in ASCENDING is from B8 to B59.
The cell contains names of student.
But some cells contain values zero (because I used a formula in all cells in
this column that will return a zero value (0) if a condition is not met).

When I sort in alphabetical order (ascending), the cells with values 0 will
be listed first followed by A, B, C etc.
(0,0,0,A,B,C,)

Is there any way I can ignore the zero values in that column so that the
sorting will be A,B,C etc followed by cells with 0 values?
(A,B,C,0,0,0)

Can someone help me to modify the formula?

Thanks... I appreciate your kind help.

Warmest regards,
Salza

 

[Don Guillett]

Sent privately
Actually, Here is the refined version. Adjust your ranges to suit.
BTW You should keep all questions/answers in the NG unless invited to do
otherwise.
Sub Macro5()
With Range("A1:E12")
.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.AutoFilter
End With
End Sub

 

[Gord Dibben]

Salza

If the zeros are not used in any calculations, which in a list of student
names seems likely, why not return a blank value instead of zero
to those cells?

=IF(Cellref<>"whatever","","something else")

Blank cells will go to the bottom when sorting.

Gord Dibben Excel MVP

 

[Dave Peterson]

And to piggyback on Gord's answer:

If you don't want to change the existing formulas (and maybe other formulas that
point at these cells), maybe you can insert a helper column and sort on that:

Put something like:

=if(a2=0,rept("z",255),a2)
and drag down.

Or use whatever character that will group the data the way you want.

 

[Don Guillett]

OP came back to me and said what I sent is what was desired.