+ Reply to Thread
Results 1 to 4 of 4

Cannot Sort Range VBA - "Unable to get sort property of range class"

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    154

    Cannot Sort Range VBA - "Unable to get sort property of range class"

    I am trying to sort a range in VBA and have tried numerous ways of identifying the range without success. I can't figure out why I get the run-time error on the sortfields.clear line (also when I inactivate this line I get the same error on the next line)

    Sub Lease_Options()
        
        Dim LastOptionRange As String
       
       
        Range("A4").Select
    
        ActiveCell.End(xlToRight).End(xlDown).Select
        LastOptionRange = ActiveCell.Address(1, 1)
        
    
         Range("$A$4:" & LastOptionRange).Sort.SortFields.Clear
        Range("$A$4:" & LastOptionRange).Sort.SortFields.Add _
            Key:=Range("C5", Range("C5").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        
       
        With Range("$A$4:" & LastOptionRange).Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
    End Sub
    pls click the star if you liked my answer!

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Cannot Sort Range VBA - "Unable to get sort property of range class"

    Though I have limited knowledge on the subject, i think using .SortFields requires a Worksheet or ListObject object. I don't think you can use a range. Changing the range to a worksheet allows that line to work for me.

  3. #3
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    154

    Re: Cannot Sort Range VBA - "Unable to get sort property of range class"

    ah that was it. thx!

  4. #4
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: Cannot Sort Range VBA - "Unable to get sort property of range class"

    You should check that the last data column to the right does not have all empty cells below the top cell; if so, the selected area goes all the way to row 1048576. That large range may be causing the runtime error for SORT.

    Just a thought.
    -Stu

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Error: "Unable to get the interior property of the range class."
    By wunderfisch in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-18-2013, 03:18 PM
  2. Error on Sheet Name Change: "Unable to set the Hidden Property of the Range Class"
    By mandora in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-23-2013, 06:10 PM
  3. Run-time error 1004; "Unable to set the Hidden property of the Range Class"
    By danimal_time in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-22-2012, 01:06 PM
  4. "Unable to set FormulaArry property of the range class" - Error
    By drgogo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2010, 02:19 PM
  5. "Unable to set the Hidden property of the Range class" when unhiding cells
    By Kaigi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2009, 03:33 PM

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