Sorting values in dynamic range

  • Hi everyone,


    I've recorded macros to sort values in the fixed size range and failed to adjust it to flexible/dynamic range.

    I tried to replace line: .SetRange ActiveSheet.Range("A1:G21")

    with line: .SetRange ActiveSheet.Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select

    It did not work and gives Run-time error "13": Type mismatch.

    I would be grateful for any help. Thanks in advance.

    Dilshod


  • Hello,


    You could test the following

    Code
    1. Sub SortValues()
    2. Dim last As Long
    3. last = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    4. Range("A1:G" & last).Sort [A1], xlAscending, Header:=xlYes
    5. End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    You could test the following

    Code
    1. Sub SortValues()
    2. Dim last As Long
    3. last = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    4. Range("A1:G" & last).Sort [A1], xlAscending, Header:=xlYes
    5. End Sub


    Hope this will help

    Hi Carim,


    Thanks a lot! It works and it is less bulky and looks more elegant. Appreciate it.

  • Hi Carim,


    Thanks a lot! It works and it is less bulky and looks more elegant. Appreciate it.


    Glad this could help ...


    Thanks for your Thanks :) AND also for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)