Results 1 to 6 of 6
  1. #1
    FenceFurniture's Avatar
    FenceFurniture is offline The prize lies beneath - hidden in full view
    Join Date
    Oct 2010
    Location
    1017m up in Katoomba, NSW
    Posts
    10,649

    Default Question on a piece of VB code for Excel sort

    G'day

    I have a sort that has a problem. It is attached to a macro button, and I make my selection or rows in the sheet, run the macro, sorting the rows by columns D and G (so G within D). It's actually two more columns than that, but never mind for this question.

    When I do the sort manually and record it (macro) it adds this:
    DataOption:=xlSortTextAsNumbers

    to the answer sort numbers that look like numbers as numbers - it will not sort properly without it.


    If I put this in my abbreviated code sort:

    Selection.Sort.SortFields.Add2 Key1:=Range("D3"), SortOn:=xlSortOnValues
    it gives
    Unable to get the Sort property of the Range class

    because it wants Range"A5:AG9" if I have selected rows 5-9.


    If I put this in my abbreviated code sort:

    Selection.Sort Key1:=Range("D3"), Key2:=Range("G3"), DataOption:=xlSortTextAsNumbers
    it gives
    Application-defined or object-defined error

    because it doesn't like the ", DataOption:=xlSortTextAsNumbers" - it used to work (but incorrectly) until I added that phrase.



    What it does like is the very long winded:
    ActiveWorkbook.Worksheets("MUSIC").Sort.SortFields.Add2 Key:=Range( _
    "D118:D746"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    ActiveWorkbook.Worksheets("MUSIC").Sort.SortFields.Add2 Key:=Range( _
    "G118:G746"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("MUSIC").Sort.SortFields.Add2 Key:=Range( _
    "F118:F746"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("MUSIC").Sort
    .SetRange Range("A118:AC746")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    but I can't put specific ranges in the code - has to be Selection.

    So the question is: how to get the phrase ", DataOption:=xlSortTextAsNumbers" into the Key?
    Regards, FenceFurniture

    COLT DRILLS GROUP BUY
    Jan-Feb 2019 Click to send me an email

  2. # ADS
    Google Adsense Advertisement
    Join Date
    Always
    Location
    Advertising world
    Posts
    Many





     
  3. #2
    Join Date
    Mar 2018
    Location
    Sydney
    Posts
    1,166

    Default

    I use tables to simplify this. Format your range as a table and operate on that. That way, you don't need to select the entire range, just a portion of the range (in your case it could be G118:G119 and F118:F119)

    Here's a working example that I use, that only selects a subset of values in the table. It clears the current sort, adds a new sort, and then does the sort...
    ActiveWorkbook.Worksheets("Customers").ListObjects("tblCustomers").Sort. _ SortFields.Clear
    ActiveWorkbook.Worksheets("Customers").ListObjects("tblCustomers").Sort. _
    SortFields.Add Key:=Range("D4: D5"), SortOn:=xlSortOnValues, Order:= _
    xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Customers").ListObjects("tblCustomers").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

  4. #3
    FenceFurniture's Avatar
    FenceFurniture is offline The prize lies beneath - hidden in full view
    Join Date
    Oct 2010
    Location
    1017m up in Katoomba, NSW
    Posts
    10,649

    Default

    Thanks poundy - Apologies for the late response - been out all day since posting.

    I've never used formal tables before, but I use Ranges as tables for vlookups etc and my standard is to call them Tbl.xxxx. I looked up how to create one and have put the "Format as Table" button on my quick access toolbar, but I'm sure I'm doing something wrong. It comes up with a whole bunch of colour schemes that I definitely don't want (already highly customised formatting).

    So I clicked New Table Style and then gave it the name Tbl.UK_EU and clicked element style "Whole Table" (and didn't do any formatting). I have no idea what I'm doing there.


    What I have is
    ActiveWorkbook.Worksheets("Music").ListObjects("Tbl.UK_EU").Sort.SortFields.Clear

    and I get "Subscript out of Range"
    Regards, FenceFurniture

    COLT DRILLS GROUP BUY
    Jan-Feb 2019 Click to send me an email

  5. #4
    Join Date
    Mar 2018
    Location
    Sydney
    Posts
    1,166

    Default

    Just looking at my code to see what's the difference.... can you go into name manager and confirm what it's range is ?

    I just mocked up something as an example and then recorded a macro. So seems like it should work ? I removed the select and that also worked so the range is what i suspect...

    Range("tbl.eu_uk[#All]").Select
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("tbl.eu_uk").Sort.SortFields. _
    Clear

  6. #5
    FenceFurniture's Avatar
    FenceFurniture is offline The prize lies beneath - hidden in full view
    Join Date
    Oct 2010
    Location
    1017m up in Katoomba, NSW
    Posts
    10,649

    Default

    I got to thinking a bit last evening, and solved it a slightly different way, but still using a Table range (although not strictly as a Table).
    There are seven sections each with a dividing header, so I set up 7 ranges, and then called the sort and passed a parameter to it. It doesn't matter if the whole (1/7) range is sorted each time.

    Once I had it working properly I then stripped out all the default sort info (as is my want) to make it easier to read.

    Sub SORT_ColE_ColG()

    If Not Application.Calculation = -4135 Then Application.Calculation = -4135
    Application.ScreenUpdating = False: Application.EnableEvents = False
    rw_save = Selection.Row
    If rw_save > Range("au_nz").Row And rw_save < Range("uk_eu").Row Then: Tbl = "Tbl.AU_NZ": Call SORT_NON_Class(Tbl) 'to sort by by D,G,F,E
    etc for the other 5 ranges, and then
    If rw_save > Range("clss").Row And rw_save < Range("perf_only").Row Then Tbl = "Tbl.CLASS": Call SORT_CLASS(Tbl) 'to sort by D,E,F,G

    ActiveWindow.ScrollRow = rw_save - 10 'to get me back to where I started
    If Not Application.Calculation = -4105 Then Application.Calculation = -4105
    Application.ScreenUpdating = True: Application.EnableEvents = True
    End Sub


    Sub SORT_NON_Class(Tbl)

    Sheets("MUSIC").Sort.SortFields.Clear
    Sheets("MUSIC").Sort.SortFields.Add2 Key:=Range("D3")
    Sheets("MUSIC").Sort.SortFields.Add2 Key:=Range("G3"), DataOption:=xlSortTextAsNumbers
    Sheets("MUSIC").Sort.SortFields.Add2 Key:=Range("F3"), DataOption:=xlSortTextAsNumbers
    Sheets("MUSIC").Sort.SortFields.Add2 Key:=Range("E3")
    With Sheets("MUSIC").Sort: .SetRange Range(Tbl): .Header = xlYes: .Apply: End With
    End Sub

    EDIT:
    Now I can strip the sort back even further:

    Sub SORT_NON_Class(Tbl)

    With Sheets("MUSIC").Sort.SortFields
    .Clear: .Add2 Key:=Range("D3"): .Add2 Key:=Range("G3"), DataOption:=xlSortTextAsNumbers
    .Add2 Key:=Range("F3"), DataOption:=xlSortTextAsNumbers: .Add2 Key:=Range("E3")
    End With
    With Sheets("MUSIC").Sort: .SetRange Range(Tbl): .Header = xlYes: .Apply: End With
    End Sub

    So thanks for your help poundy - it tipped me in the right direction
    Regards, FenceFurniture

    COLT DRILLS GROUP BUY
    Jan-Feb 2019 Click to send me an email

  7. #6
    FenceFurniture's Avatar
    FenceFurniture is offline The prize lies beneath - hidden in full view
    Join Date
    Oct 2010
    Location
    1017m up in Katoomba, NSW
    Posts
    10,649

    Default

    BTW, this is what I get when I click the Format Table button.

    format table.JPG
    Regards, FenceFurniture

    COLT DRILLS GROUP BUY
    Jan-Feb 2019 Click to send me an email

Similar Threads

  1. Question on Conditional Formatting Excel 2016
    By FenceFurniture in forum COMPUTERS
    Replies: 13
    Last Post: 13th September 2019, 08:47 PM
  2. Question: How to Sort/Filter Market Place/Auctions Pages ?
    By RoyG in forum FORUMS INFO, HELP, DISCUSSION & FEEDBACK
    Replies: 0
    Last Post: 4th February 2016, 12:55 PM
  3. Excel Question
    By Chris Parks in forum COMPUTERS
    Replies: 5
    Last Post: 30th July 2011, 12:03 AM
  4. Excel question
    By Daddles in forum COMPUTERS
    Replies: 26
    Last Post: 6th June 2007, 12:29 AM
  5. hollowing tools question (and my latest piece)
    By TimberNut in forum WOODTURNING - GENERAL
    Replies: 3
    Last Post: 8th June 2005, 11:39 PM

Posting Permissions

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