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

Sub SortValues()

    ActiveSheet.Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=ActiveCell.Range _
        ("A1:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange ActiveSheet.Range("A1:G21")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Quote from Carim

Hello,

You could test the following

Code

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

Hope this will help

Hi Carim,

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