45

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
Teamothy
  • 2,000
  • 3
  • 16
  • 26
Developer
  • 4,158
  • 5
  • 34
  • 66

4 Answers4

101

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
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • 2
    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. – Yasskier Nov 15 '15 at 20:22
  • 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. – Syntax Junkie Apr 18 '23 at 15:34
16

Or this:

Range("A2", Range("D" & Rows.Count).End(xlUp).Address).Sort Key1:=[b3], _
    Order1:=xlAscending, Header:=xlYes
L42
  • 19,427
  • 11
  • 44
  • 68
  • I like this answer since it does not involve an additional variable – sonyisda1 Sep 02 '16 at 14:31
  • 7
    @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. – Kami Kaze Feb 23 '17 at 13:52
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

Sujoy
  • 1,051
  • 13
  • 26
-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
Simi
  • 5
  • 1
  • 13
    [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Wolfie Aug 29 '17 at 11:42