Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Automatic sorting

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi it's me again,

    I've got a table containing 12 columns. One of the columns data is a mixture of % and text. I need to sort this data in ascending order whereby the highest % is at the top and below that Id like to see the text in ascending alphabetical order. When a new record is entered I need it to be automatically placed in the right ascending order. How do I go about it?

    Many tks,

    Matti.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Sheffield, UK
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, here is one way:

    First, record a macro to sort your data ascending by your column (lets imagine in your table, the titles are in row 1 and the data is in rows 2,3,... Also, lets assume the column you want to sort by is columb B)

    This gets the percentages to the top (but the wrong way round) and the text as it should be. Next, use a piece of code like this:

    myrow = 1
    Do
    myrow = myrow + 1
    Loop Until IsNumeric(Range("B" & myrow)) = False

    This will tell you where the numbers end. Now, just copy and paste your sort routine in again, but change the last row to myRow-1. (and sort to descending)

    It will look something like this:

    Sub mysort()
    Range("a2:L100").Select
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    myrow = 1
    Do
    myrow = myrow + 1
    Loop Until IsNumeric(Range("B" & myrow)) = False

    Range("A2:L" & myrow - 1).Select
    Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    End Sub
    Hope this is some help

    [ This Message was edited by: philR on 2002-05-09 07:26 ]

    [ This Message was edited by: philR on 2002-05-09 07:27 ]

    [ This Message was edited by: philR on 2002-05-09 07:37 ]

    [ This Message was edited by: philR on 2002-05-09 07:38 ]

Some videos you may like

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
  •