Code To Sort Specific Range

[timbo1957]

I need to sort from the bottom row of the spreadhseet up to and including row 5 by the values in column X Ascending order could someone give me a piece of code that would do this please?

[Kyle123]

Morning,

Can't you just click on the column and sort Z>A?

[timbo1957]

No that will include the header rows, I need something will only sort from row 5 down on a spreadhseet that is constantly changing length, hence from the bottom up to row 5.

[Kyle123]

How may columns do you have?

[timbo1957]

125 columns in total, rows currently 309 (that includes the first 4 rows I don't want sorted)

[Kyle123]

Try something like this:

Public Sub sortAsc()
Dim lr As Long

Sheet1.AutoFilterMode = False
lr = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row

With Sheet1.Range("A5:O" & lr)
    .AutoFilter
    .sort Key1:=Range("D5"), Order1:=xlAscending
End With

Sheet1.AutoFilterMode = False

End Sub

[timbo1957]

Hi Kyle123

That doesn't work, I have changed the ranges to suit as follows -

   Dim lr As Long

    Sheet1.AutoFilterMode = False
    lr = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row ' Changed to B Column A is blank

    With Sheet1.Range("A5:DU" & lr) 'Expanded to Column DU
        .AutoFilter
        .Sort Key1:=Range("X5"), Order1:=xlAscending 'Changed to Ascending
    End With

    Sheet1.AutoFilterMode = False

[watersev]

hi, timbo1957, try this option:

Sub test()

Application.ScreenUpdating = 0

With Range("a5", Cells(Rows.Count, "du").End(xlUp))

    .Sort key1:=Range("x5"), order1:=xlAscending, header:=xlNo

End With

Application.ScreenUpdating = 1

End Sub

amend last row definition to your actual data layout

[timbo1957]

Thanks watersev, worked a treat!