Sort range ascending

[verluc]

Is the following possible in Excel:

Select range : A4 to last row and R4 to last row
Then sort this range ascending with column D
Many thanks in advance for any help.

 

[Andrew Poulsom]

The macro recorder will get you started. Post the code you get if you are still stuck.

 

[verluc]

Andrew,

This is the macro I get with the macro recorder.The problem is that the row to R247 change every day.
So to the "last row" in R is much better.

Sub Macro1()
Range("A4:R247").Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range _
("D4:D247"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A4:R247")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Andrew Poulsom]

Try:

Sub Macro1()
    Dim LR As Long
    With ActiveWorkbook.Worksheets("Data")
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range _
            ("D4:D" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With .Sort
            .SetRange .Parent.Range("A4:R" & LR)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

When posting VBA code please use Code tags.

 

[verluc]

Million thanks, Andrew, it works like I wanted !!!

(where can I add the Code tags ?)

 

[Andrew Poulsom]

Use the # button at the top of the Reply window.

 

[verluc]

Andrew,

A little additional question : on my excel program 2010 it works excellent, but on an excel version 2003 I get
with this macro the error 438 on the field : .Sort.SortFields.Clear
Any idea?

 

[Andrew Poulsom]

Excel 2003 doesn't have a Sort object. The macro recorder will give you some code that you can adapt.