excel 宏（VB）实现复制一列并按第二列的单元格长度排序

最新推荐文章于 2022-11-28 23:08:04 发布

inurlcn

最新推荐文章于 2022-11-28 23:08:04 发布

阅读量1.6k

点赞数

本文链接：https://blog.csdn.net/inurlcn/article/details/49043351

版权

Sub 复制排序()

    Columns("B:B").Select
    Selection.Copy
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("E1").Select
    Application.CutCopyMode = False
    

'排序的话应该有更好的方式，直接按单元格长度排序，没时间弄

    Dim t As Double


    t = ActiveSheet.[A1048576].End(xlUp).Row '针对EXCEL2010
    
    
    ActiveCell.FormulaR1C1 = "=LEN(RC[-2])"
    Range("E1").Select
    Selection.Copy
    Range(Cells(t, 5), Cells(1, 5)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
        ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("E1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range(Cells(1, 1), Cells(t, 5))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Save
    
    
End Sub