D
Don Guillett
How about filtering out the 0's before or after the sort
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
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
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
D
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
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
G
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
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
D
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.
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.
D
Don Guillett
OP came back to me and said what I sent is what was desired.