none
Excel vba sorting on a changing range RRS feed

  • Question

  • I have the following code:

    Dim rRange As Range
    Dim rColA As Range
    Dim rRangeName As Variant
    Dim rColAName As Variant
    Dim rActCell As Variant
    Dim rActCellAddr As Variant

    'Delete Previous Range Names
    On Error Resume Next
    Set rRange = Range("RawData")
    On Error GoTo 0
    If Not rRange Is Nothing Then
        ActiveWorkbook.Names("RawData").Delete
    End If
    On Error Resume Next
    Set rColA = Range("ColumnA")
    On Error GoTo 0
    If Not rColA Is Nothing Then
        ActiveWorkbook.Names("ColumnA").Delete
    End If

    'Define Range Name for Column A
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="ColumnA", RefersTo:=Selection
    rColAName = "ColumnA"

    'Define Range Name for Raw Data
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="RawData", RefersTo:=Selection
    rRangeName = "RawData"
    Range("A2").Select

    'Get cell address for bottom of data area
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.End(xlToRight).Select
    rActCell = ActiveCell.Address(0, 0)
    rActCellAddr = "A1:" & rActCell

    ' Goto Named Range
    Application.Goto Reference:="RawData"

    rColAName = "ColumnA"
    ' Sort Named Range "RawData" on Column C Descending
    ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=ActiveCell.Offset(0, 2).Range(rColAName), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
        .SetRange ActiveCell.Offset(-1, 0).Range(rActCellAddr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    I get a Runtime error 1004 on the line:

       .SetRange ActiveCell.Offset(-1, 0).Range(rActCellAddr)

    Can't find anything online to assist. Can you help?

    Sunday, May 24, 2015 10:44 PM

Answers

  • Re:  SetRange error

    You select Range("A1") and then tell Excel to move up one row... "ActiveCell.Offset(-1, 0)"
    There is no row above A1.

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Monday, May 25, 2015 6:02 AM
  • Hello:

    I think Jim Cone hit the nail on the head.  As a standard practice, I always like to initialize a range variable BEFORE I issue the .SetRange command.  That way, you will know if your range setting was improper.

    For example:

    Set rngToSort = Range(wksMasterCustomerList.Cells(2, 1), wksMasterCustomerList.Cells(lngLastRowOfCustomerMaster, lngLastColumnOfCustomerMaster))
    With wksMasterCustomerList.Sort
        .SortFields.Clear
        .SortFields.Add Key:= _
            Columns("A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .SetRange rngToSort
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    I wrote an entire book on the subject of VBA Sorts and Searches entitled "Power Up Using Excel VBA Sorts and Searches" published on Amazon.com.


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, May 26, 2015 3:30 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Monday, May 25, 2015 5:22 AM
  • Re:  SetRange error

    You select Range("A1") and then tell Excel to move up one row... "ActiveCell.Offset(-1, 0)"
    There is no row above A1.

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Monday, May 25, 2015 6:02 AM
  • Hello:

    I think Jim Cone hit the nail on the head.  As a standard practice, I always like to initialize a range variable BEFORE I issue the .SetRange command.  That way, you will know if your range setting was improper.

    For example:

    Set rngToSort = Range(wksMasterCustomerList.Cells(2, 1), wksMasterCustomerList.Cells(lngLastRowOfCustomerMaster, lngLastColumnOfCustomerMaster))
    With wksMasterCustomerList.Sort
        .SortFields.Clear
        .SortFields.Add Key:= _
            Columns("A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .SetRange rngToSort
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    I wrote an entire book on the subject of VBA Sorts and Searches entitled "Power Up Using Excel VBA Sorts and Searches" published on Amazon.com.


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, May 26, 2015 3:30 AM