Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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..

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    (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#>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  8. #8
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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..

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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>


  10. #10
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 :-))

  11. #11
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 ?

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  14. #14
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 :-))

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •