VBA Excel sort range by specific column

Question

I have a table that can contain any number of rows:

enter image description here

As I said it can contain 1 or ∞ rows.

I want to sort range A3:D∞ by the Date cell that is in column B.
How can I do it?

The problem is that I don't know how to select from A3 to the last row.

I think that looping to the last row is not a correct method.

I have got this so far it sorts looks like correct, but the range is hard-coded.
How do I get rid of the hard-coding of the range?

Range("A3:D8").Sort key1:=Range("B3:B8"), _
order1:=xlAscending, Header:=xlNo

Dmitry Pavliv · Accepted Answer · 2014-01-30 08:44:00Z

102

Try this code:

Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A3:D" & lastrow).Sort key1:=Range("B3:B" & lastrow), _
   order1:=xlAscending, Header:=xlNo

answered Jan 30, 2014 at 8:44

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yasskier Over a year ago

Just a note: this doesn't work too well with datetimes data type, which are sorted as strings. Its safer to convert the fields to long and then sort.

Syntax Junkie Over a year ago

It would be nice if this answer explained why the OP's code didn't work and then explains why this code given does work.

Juuso Nykänen Over a year ago

It is enough to specify any one cell in the column used for sorting and removes the need for dynamic tracking of number of rows for the key1 parameter. Range("A3:D" & lastrow).Sort key1:=Range("B4"), order1:=xlAscending, Header:=xlNo works here too

L42 · Accepted Answer · 2014-01-30 08:56:21Z

16

Or this:

Range("A2", Range("D" & Rows.Count).End(xlUp).Address).Sort Key1:=[b3], _
    Order1:=xlAscending, Header:=xlYes

answered Jan 30, 2014 at 8:56

L42

19.8k11 gold badges47 silver badges70 bronze badges

2 Comments

sonyisda1 Over a year ago

I like this answer since it does not involve an additional variable

Kami Kaze Over a year ago

@sonyisda1 that's a bad reason, you could just replace the variable with the formula. But it is more clear what happens with the variable.

Sujoy · Accepted Answer · 2022-06-18 17:15:18Z

0

You can sort any range in a very dynamic way by using the SortRange and GetCurrentRegionStartingGivenCell function in Xatocode as follows:

' First you may define a worksheet level named range in cell "A2" and name it as rngData
Sub SortExample()

    Dim rngData         As Range ' Range to sort

    Set rngData = GetCurrentRegionStartingGivenCell(shtData.Range("rngData"))  
    Call SortRange(rngData, True, 2, xlAscending, 3, xlDescending)

End Sub

You can read the complete article here

answered Jun 18, 2022 at 17:15

Sujoy

1,24718 silver badges27 bronze badges

Comments

Simi · Accepted Answer · 2015-09-14 20:26:56Z

-2

If the starting cell of the range and of the key is static, the solution can be very simple:

Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort key1:=Range("B3", Range("B3").End(xlDown)), _
order1:=xlAscending, Header:=xlNo

answered Sep 14, 2015 at 20:26

Simi

51 bronze badge

1 Comment

Wolfie Over a year ago

How to avoid using Select in Excel VBA

Collectives™ on Stack Overflow

VBA Excel sort range by specific column

4 Answers 4

3 Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related