CODEWITHSUNDEEP
vbaexcelsorting
rakesh seebaruth
rakesh seebaruth

Reputation: 69

How to sort a column from newest to oldest?

I have the following VBA code:

Sub sort()
    Range("M2:M").Sort _
    Key1:=Range("M2"), Order1:=xlAscending
End Sub

But, it's not working. How do I fix it?

Upvotes: 1

Views: 14015

Answers (1)

user4039065
user4039065

Reputation:

It's not working because you are using a syntax on the column; wants a last row specified. The Sort also want the primary key to be the first cell in the "M2:M" & lr range. Telling it M2, relative to Range("M2:M" & lr) is actually referencing Y3. You should know if you are using a header or not; I'm assuming any header label is in M1 so for Range("M2:M" & lr) you use Header:=xlNo.

Sub msort()
    dim lr as long
    lr = cells(rows.count, "M").end(xlup).row
    with Range("M2:M" & lr)
        .Sort Key1:=.cells(1), Order1:=xldescending, _
              Orientation:=xlTopToBottom, Header:=xlNo
    end with
End Sub

You should also avoid reserved words for the names of your sub procedures and variables. Get into the habit of providing parent worksheet references.

btw, dates ordered newest to oldest are in an xldescending order, not xlascending.

Upvotes: 3

Related Questions