sort without limiting range

acat · Oct 30th 2019

Recorded a macro to sort a list, but it has this line in it - Range ("W2:W16")
Next time list likely to have different number of rows

What should the code be to not be limited to a set range?

Code

ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("W2:W16"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Display More

**Carim** · Oct 30th 2019

You could replace 16 by your own variable

Code

Dim last As Long
last = Worksheets("Sheet1").Cells(Rows.Count, 23).End(xlUp).Row

Hope this will help

acat · Oct 30th 2019

what does 23 stand for?

**Carim** · Oct 30th 2019

It stands for W

**Carim** · Oct 30th 2019

You are welcome

acat · Oct 30th 2019

20 8 1 14 11 19

**Carim** · Oct 30th 2019

:lol:

sort without limiting range

Participate now!

Share