时间戳

首页 > 解决方案 > 按日期对行进行排序

excel - 按日期对行进行排序

问题描述

我是 VBA 新手，我的表有问题。如果我的表格有一个标题并且日期从 A2 列开始，表格大小最多为 R。我需要一种按日期自动对行进行排序的方法。表大小不断增长，因此如果需要，如何扩展选择是可取的。

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
    "A2:A" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:Q" & lastrow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveCell.SpecialCells(xlLastCell).Select

结束子

标签： excelvbasortingdaterows

解决方案

Put your data in an actual table - highlight the data and select Insert ~ Table from the toolbar.
Your table should be called Table1 (you can manually change this).

Sub Test()

    Dim MyTable As ListObject
    Set MyTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    
    With MyTable
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 _
            Key:=Range("Table1[MyDateColumn]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal 'Change MyDateColumn to the name of your date column.
            
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With
    
End Sub

You don't need to include the xlPinYin method

推荐阅读