Sorting with negative number ...again.. (2000)

[NiuB]

Hello again guys..

Actually this problem has been solve by steve and andrew 2 weeks ago..

But at that time , i do it on a small range and the code taht provided by steve and andrew works..

But when i try expanding the range..it happened again..

I dont know what went wrong..

When i try to sort using column AH..theres a gap between the data..

please help me ad refer to the attachment..thanks

[HansV]

If you select Tools | Options... and activate the View tab, you will see that the Display Zeros check box is clear. This means that zero values will be hidden. If you tick this check box and click OK, you will see that the empty-seeming cells in columns W, Z and AH actually contain zeros. Therefore, the cells in column AH are sorted between the negative and positive values. If you clear the zeros, sorting will proceed as you expected.

[NiuB]

Thanks Hans for the answer..

But what FYI , the real database is , i copy that data from other sheet and paste it using

paste special into the seet that i senta as an attachment

I know about that zero value because it is my intention to hide it..

But 2 weeks ago steve have give me a code to sort between negative numbers and positive numbers

it works fine.. The code is :

Sub GlobalSusunGred()
Sheets("GlobalKelas").Activate
Dim strPwd As String
strPwd = ""



Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=strPwd
Sheets("GlobalKelas").Select
Range(Range("AH6"), Range("B65536").End(xlUp)).Select



ActiveSheet.Unprotect Password:=strPwd
Selection.Sort Key1:=Range("ah6"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


Sheets("GlobalKelas").Select
Range("b7").Select


Application.ScreenUpdating = True

End Sub

it works fine with that range and that sheet..with above code , i manage to sort range B7:AH48 easily
and without any problem

But when i expand my range to B7:AH216 and using other sheet , its not working

I feel very weird..thanks..

[NiuB]

(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

For reference , this is my post about this thing a couple weeks ago and have been solved by steve and andrew

But it occured again when i expand my range and do it in a new sheet..Any ideas ? Thanks

Below is the link to my question :
<post#=364542>post 364542</post#>

[HansV]

But why do you want those zeros? Your problem disappears entirely if you clear them.

The code you post here, and the code in the post you referred to doesn't work correctly for me in theTest1.xls workbook from your thread in April, so I have no idea how to adapt it for the new one. Sorry.

[NiuB]

Thanks again Hans for the response....

To answer your question , ia have to attach another wb in order to make you understand what

am i doing with those 0 value..please refer to my attachment..thanks

[HansV]

I'm sorry, that leaves me completely in the dark. Perhaps someone else can help.

[NiuB]

So i'm not alone..hehe..(completely in the dark )

Anyway..thanks hans for the effort

I really hope somebody can help me solve this saga..

[sdckapr]

Try this. It sorts first by COl L to get the "blanks at the bottom". Then adjusts the range to only sort the "non-blank" entries in the second sort on AH.

Steve

<pre>Sub GlobalSusunGred()
Sheets("GlobalKelas").Activate
Dim strPwd As String
strPwd = ""

Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=strPwd
Sheets("GlobalKelas").Select

ActiveSheet.Unprotect Password:=strPwd
Range(Range("AH6"), Range("B65536").End(xlUp)).Sort _
Key1:=Range("L6"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range(Range("AH6"), Range("L65536").End(xlUp)).Sort _
Key1:=Range("ah6"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets("GlobalKelas").Select
Range("b7").Select

ActiveSheet.Protect Password:=strPwd
Application.ScreenUpdating = True

End Sub</pre>

[NiuB]

Thanks Steve for the brilliant ideas..cheers..

So far it works like charm..but i still want to test it with many combination of marks and grades..

If any problem occur again , surely i will post it..thanks a zillion steve :-))

[NiuB]

Hello again steve...

As i promised before , i wiill post if any problem persist and yet is true.

The code you provide works ok steve.

But after i put some data and put it randomly i mean ie..in my attachment , i put nama and marks

from no1 too no 27 , uing your code i can sort easily..

But when i add data add no 30..so i leave no 28 , 29 blank..and then i sort..

All data are scramble up..ii dont know what happen.do you have any idea ?

[sdckapr]

OOPS <img src=/S/blush.gif border=0 alt=blush width=15 height=15> i sorted with only L-AH, I forgot to grab column B for the second sort:
Modify the line segment:
<pre>Range(Range("AH6"), Range("L65536").End(xlUp)).Sort _</pre>

to
<pre>Range(Range("AH6"), Range("L65536").End(xlUp).Offset(0, -10)).Sort _</pre>


Steve

[sdckapr]

I made some other changes to "clean it up a little. You had some redundant code and some "unneeded selecting" in the code.

<pre>Option Explicit
Sub GlobalSusunGred()
Dim strPwd As String
strPwd = ""

Sheets("GlobalKelas").Activate ' not needed
Application.ScreenUpdating = False
With Sheets("GlobalKelas")
.Unprotect Password:=strPwd
.Range(.Range("AH6"), .Range("B65536").End(xlUp)).Sort _
Key1:=.Range("L6"), Order1:=xlDescending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range(.Range("AH6"), .Range("L65536").End(xlUp).Offset(0, -10)).Sort _
Key1:=.Range("ah6"), Order1:=xlDescending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
.Protect Password:=strPwd
End With
Application.ScreenUpdating = True
End Sub</pre>



You could even eliminate the line:
<pre> Sheets("GlobalKelas").Activate</pre>


and not even activate this sheet and still sort it. The code then makes the active sheet in the workbook immaterial as it works on the "GlobalKelas" sheet and does not select anything

Steve

[NiuB]

Hi again steve ,

It works again.Hope it is final..hehe..so i wont bother you again with the same problem..lol..lol

Steve..could you please elaborate to me...what actually the code perform..

What different between the normal sort macro and the one you did it for me ?

I'm also confuse about the "offset"

Could you spare your time expalin to me what offset means and give some examples...

Zillion thanks steve..regards :-))

[sdckapr]

You sorted the range from B to AH on Column AH:

The problem is that "blank" rows have zeroes in the AH column that you wanted to keep. negatives come after 0 so the "blanks" are within the data.

I did 2 sorts, the first sort includes the whole range (same as yours) but sorts on Col L instead of AH. Col L is blank in the "blank rows" while AH has zero and B also has text. This puts the blanks in the bottom.

Next I select all the rows that have values in L:AH (i fixed this later) to include B:AH when L has values. My original post only picked L:AH when L had values [L65536.end(xlup)], The offset 0,-10 just picks up the 10 columns to the left of L (going to Col [img]/forums/images/smilies/cool.gif[/img]

.Range("L65536").End(xlUp).Offset(0, -10)

Says "go to L65536" (the last row of col L) then go up until you come to a cell with something in it. Offset (0, - 10) is from this row in L, do not change rows (0), but move 10 columns to the left (-10). This puts in col B in the last row that has values in L. I sort on this range using Col AH so I ignore the zero values in the "blank rows"

Hope this helps,
Steve