I have a table that can contain any number of rows:
我有一个可以包含任意行数的表:
As i said it can contain 1 or ∞ rows.
就像我说的它可以包含1或∞行。
I want to sort range A3:D∞ by the Date cell that is in column B. How can I do it?
我想范围A3:D细胞∞的日期列b。我能怎么做?
The problem is that I don't know how to select from A3 to the last row.
问题是我不知道如何从A3到最后一行。
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 hardcoded. How do I get rid of the hardcoding of the range?
到目前为止,我已经完成了排序,看起来是正确的,但是范围是硬编码的。如何摆脱范围的硬编码?
Range("A3:D8").Sort key1:=Range("B3:B8"), _
order1:=xlAscending, Header:=xlNo
3 个解决方案
#1
59
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
#2
11
Or this:
或:
Range("A2", Range("D" & Rows.Count).End(xlUp).Address).Sort Key1:=[b3], _
Order1:=xlAscending, Header:=xlYes
#3
0
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
#1
59
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
#2
11
Or this:
或:
Range("A2", Range("D" & Rows.Count).End(xlUp).Address).Sort Key1:=[b3], _
Order1:=xlAscending, Header:=xlYes
#3
0
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