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
Code
- 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