Autosorting
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Autosorting

  1. #1
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Over a month ago someone wrote in about this subject and the follwing code was furnished.

    "Using something like this

    Code:


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
    Range("A1:A5").Sort Range("A1"), xlAscending, Header:=xlYes
    End If
    End Sub


    In Here, I'm sorting Range("A1:A5") each time one of the cells changes. I'm also assuming it has a title.

    End of supplied code"

    My confusion is the range. I would have to increase the range for my sheet, but how can I make the range grow as my rows increase?


  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi


    I would suggest a dynamic named range (well worth learning) and then using something like:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("MyDynaRange")) Is Nothing Then
    Range("MyDynaRange").Sort Range("A1"), xlAscending, Header:=xlYes
    End If
    End Sub

    See: http://www.ozgrid.com/Excel/DynamicRanges.htm

    Or a second preference might be:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1", Range("A65336").End(xlUp))) Is Nothing Then
    Range("A1", Range("A65336").End(xlUp)).Sort Range("A1"), xlAscending, Header:=xlYes
    End If
    End Sub


    If the growing/shrinking range is column A





    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-19 07:21 ]

    [ This Message was edited by: Dave Hawley on 2002-03-19 07:25 ]

  3. #3
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the prompt reply.
    I am using '97
    I entered your 2nd code and tested. No joy.
    Message pop-up says Ambigious Name detected Worksheeet_Change. Help tells me Excel can not find, within VBA, veenlr3.hlp. A search of my hard drive says I have, within VBA, veendf3.hlp.
    Guidance?
    Oh by the way, went to your web site. I will spend hours there. It seems to be nicely done.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It just means you already have a Procedure with that name. Delete the old one and paste in mine by, right clicking on the sheet name tab and selecting "View Code"

  5. #5
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Again thanks. Rem'd out the other code. (Thanks for the right click view code tip)

    Column "A" range starts at A6.
    Column "B" is the field I want sorted.
    I changed your code. Mine says "A6" for o/a range and SortRange is "B6"

    Test in a new number at "B" and it does not resort after all data has been stroked in and I press enter.



  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No too sure I follow waht you have said, but try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B6", Range("B65336").End(xlUp))) Is Nothing Then
    Range("B6", Range("B65336").End(xlUp)).Sort Range("B6"), xlAscending, Header:=xlYes
    End If
    End Sub


    As I said intitially the Dynamic range option would be best.



  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think I recongize this code

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
    Range("A1:A5").Sort Range("A1"), xlAscending, Header:=xlYes
    End If
    End Sub

    I don't feel (Altough agree with Dave, it's worth learning them !) that a Dynamic range is needed here... just a little object programming:

    Try this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng as Range
    Set Rng = Range("A1","B" & Range("A65536").End(xlUp).Row)

    If Not Intersect(Target, Rng) Is Nothing Then
    Rng.Sort Range("A1"), xlAscending, Header:=xlYes
    End If
    End Sub

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you both.
    It still is not quite right. And I apologize for not providing the following detail previously.
    A1:L5 contain various things as instructions, title, date, down arrows for filters etc. The column "headers" appear on A5:L5
    Actual data begins on A6:L6
    The field that needs to be sorted starts at B6.
    Both of your codes sort column B. They (the codes) do not drag the other data along with it. When job number in Column "B" moves to the correct position, it replaces another number in the same column, and the other data stays in the same place. So the job number for one client now has the data for a different client.

    My wish list would be to keystroke in all data Ax:Lx (below A5:L5) press enter,tab or mouse click to the next row,and have that entire line of data sort to the correct position based on the number in column "B"

    Thanks for your guidance

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this one.
    This will not sort anything before column L is changed / written to. Then it will sort on column B / A /C from row 6 and down.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 12 And Target.Row > 5 Then
    Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Key2:=Range("A6") _
    , Order2:=xlAscending, Key3:=Range("C6"), Order3:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End If
    End Sub

    regards Tommy


  10. #10
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Tommy, this code does not seem to work at all.
    I can see that it waits for something in column 12 in any row greater than 5, but when I get to 12, and press enter, the entire line stays put.

    If only data in columns "A", "B" and "C" are mentioned, how does the other data (the stuff in "E"-"N") know to move when it sorts?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com