Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting by last digit (XP)

    Is it possible using vba to sort a series of numbers based on the last digit? For example 12345, 24680 35478 being sorted in descending order based on the last digit would be 35478,12345, 24680. All numbers would be 9 digits in length. I can use the right function [=Right(a1,1)] which will give me the last digit in column b and then do a sort based upon that column. Just wondering how I can put it all together in vba so that when it is run the sorting will also take place.

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

    Re: Sorting by last digit (XP)

    What exactly would you want to do in VBA? Please provide detailed information.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by last digit (XP)

    The nine digit numbers are in column A. In column B I have =RIGHT(A1,1) which will put the last digit into column B. I then select the two columns and sort based upon the digit in column B. What I would like in VBA is to have the formula and the sorting to be able to be done by running the code. Possibly also have column B get deleted after the code is run.

    Column A Column B
    123456789 9
    122456783 3
    312456888 8
    456789019 9

    When sorted column A would be
    456789019
    123456789
    321456888
    122456783

    The client just wants to be able to have the numbers sorted based on the last digit. Why I do not know. Something to do with grouping by the last digit.

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

    Re: Sorting by last digit (XP)

    Try this:
    <code>
    Sub SortOnLastDigit()
    Dim m As Long
    m = Cells(Rows.Count, 1).End(xlUp).Row
    Range("B:B").Insert
    Range("B1:B" & m).FormulaR1C1 = "=RIGHT(RC<!t>[-1]<!/t>,1)"
    Range("A1:B" & m).Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlNo
    Range("B:B").Delete
    End Sub</code>

  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by last digit (XP)

    Thanks. It works great.

Posting Permissions

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