查找最后一列&在最后一列上排序 [英] Find last column & sort on last column

问题描述

我需要Excel来检测我拥有的最后一列并对该列进行排序.我有一个宏，每次使用它都会生成一个新列，所以我不能使用常量.

I need Excel to detect the last column I have and sort on that column. I have a macro that generates a new column every time it is used so I cannot use a constant.

Sub sortyness() 
Dim sortdata(A1 & ":", Cells(LastRow, LastColumn)) As Range

ActiveWorkbook.Worksheets("Compiled").Sort.SortFields.Clear    
ActiveWorkbook.Worksheets("Compiled").Sort.SortFields.Add _    
    Key:=Range(Sorton), Sorton:=xlSortOnValues, Order:=xlAscending, _    
    DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Compiled").Sort    
    .SetRange Range(sortdata)    
    .Header = xlYes    
    .MatchCase = False    
    .Orientation = xlTopToBottom    
    .SortMethod = xlPinYin    
    .Apply    
End With

End Sub

这是工作表的屏幕截图:

Here's a screenshot of the sheet:

我无法按最后一列进行排序.我是否可以通过在第1行中查找没有数据的第一个单元格来定义该列，然后以此为基础进行排序?我应该如何修改我的VBA?

I am having trouble with getting it to sort by the last column. Can I define the column by looking for the first cell in row 1 that has no data and then use that as a basis to sort? How should I modify my VBA?

谢谢.

我不知道如何编辑该内容以使其不会显示为重复项，但显然不是重复项.我最关心的是在最后一列上运行宏，而不是在寻找最后一列.

I don't know how to edit this thing to get it to not appear as a duplicate, but it's obviously not a duplicate. Mine is more concerned with running a macro on the last column than it is finding the last column.

推荐答案

vba排序操作实际上所需的代码要比从录音中获取的少得多.

An vba sort operation actually requires much less code than you get from a recording.

Dim sortdata As Range, LastRow as long, LastColumn as long

With ActiveWorkbook.Worksheets("Compiled")
    LastRow = .cells(.rows.count, "A").end(xlup).row
    LastColumn = .cells(1, .columns.count).end(xltoleft).column
    with .range(.cells(1, 1), .Cells(LastRow, LastColumn))
         .Cells.Sort Key1:=.Columns(.columns.count), Order1:=xlAscending, _
                     Orientation:=xlTopToBottom, Header:=xlyes
    end with
end with

这篇关于查找最后一列&在最后一列上排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！