+ Reply to Thread
Results 1 to 14 of 14

Select 1st 2d range, sort, find 2nd, sort, etc

  1. #1
    Registered User
    Join Date
    12-22-2010
    Location
    Fort Rouge, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Select 1st 2d range, sort, find 2nd, sort, etc

    See attached.

    I want to sort each group (there are 3 groups in the sheet) and sort each by F:F, no headers.

    In the sheet 2 I show the desired result

    So in the example sheet, sort D5:l5 by F:F no headers, asc order
    Then sort D9:L11 by F:F no headers, asc order
    Then sort D15:L20 by F:F no headers, asc order

    I have sheets like this with 50 orders and I have to select and sort each.

    How can a macro do that?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    You could use this macro I tested on your 'result' sheet:
    Please Login or Register  to view this content.
    Regards,
    Antonio
    Last edited by antoka05; 12-22-2010 at 06:45 AM.

  3. #3
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    much shorter option of the code, see attachment, run macro "sort_me"

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by watersev; 12-22-2010 at 10:02 AM.

  4. #4
    Registered User
    Join Date
    12-22-2010
    Location
    Fort Rouge, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    wow watersev, that seems to work like a charm

    I'm trying to learn vba and the code is so short - would you be willing to describe how that works?

    very impressed with how compact that is


    also thank you antoka for your post

  5. #5
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    some explanation:
    1. Column "E": looking for cells with digits (analog action select column "E" and press [Ctrl+G] choose option for constants, digits). That gives us all ranges separated by coma,
    2. Parsing all those ranges by Split command and getting them in array
    3. Loop through array of ranges selecting range and sorting as required
    4. To avoid error message when cells with digits inside are absent - On error Goto Handler. Handler contains appropriate actions
    If you do study VB it would be useful to go through all commands of the code in VB help: [Alt+F11] in open Excel workbook

  6. #6
    Registered User
    Join Date
    12-22-2010
    Location
    Fort Rouge, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    Thank you

    1 I put a Watch on a (Dim a as Long) but it never had a value?
    2 I would like to evaluate f:f for digits instead of e:e, is that possible with this code and still have e:e in the array? originally I waned to sort from column D to L actually

  7. #7
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    1. Variable "a" is variant, it gets value after split
    2. Yes, it's possible to do the same trick with [f:f] with some code changes to follow as well. What do you mean "by sort from column d to l actually"? Do you want to sort appropriate range from d column to l column by which column?
    Last edited by watersev; 12-23-2010 at 03:31 AM.

  8. #8
    Registered User
    Join Date
    12-22-2010
    Location
    Fort Rouge, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    Hi Watersev,

    I am doing this again.

    Please Login or Register  to view this content.
    My sheet as over 200 groups of Constants, 1's.

    But Split stops at 18.

    If I ctrl-G and choose Constants, Numbers it selects all 200+ groups.

    Any ideas?

    Sorry, this post is old, but you were so helpful.

  9. #9
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    any chance to see workbook sample?

  10. #10
    Registered User
    Join Date
    12-22-2010
    Location
    Fort Rouge, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    find file attached
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-22-2010
    Location
    Fort Rouge, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    e243:e263 is the last range in the array

  12. #12
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    please check attachment, run code "sort me"
    Attached Files Attached Files
    Last edited by watersev; 06-23-2011 at 07:08 AM.

  13. #13
    Registered User
    Join Date
    12-22-2010
    Location
    Fort Rouge, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    thanks watersev

    what made you switch from loading an array to using the Areas property?

    you're so talented

  14. #14
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select 1st 2d range, sort, find 2nd, sort, etc

    you did .. ... areas address is very long ... looping through areas works fine

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1