排序多列excel VBA [英] sort multiple columns excel VBA

问题描述

这是我的代码

Sub SortMultipleColumns(myline As String)

    With Worksheets("Result-Inactive").Sort
        .SortFields.Add Key:=Range("A1"), Order:=xlAscending
        .SortFields.Add Key:=Range("D1"), Order:=xlAscending
        .SortFields.Add Key:=Range("J1"), Order:=xlAscending
        .SetRange Range("A1:C" & myline)
        .Header = xlYes
        .Apply
    End With

End Sub

我收到以下错误，但我不明白为什么运行时错误'1004'排序参考无效.请确保它在您要排序的日期内，然后首先按框排序不相同或空白.当我单击调试"时，.apply变得亮了

I get the following error and I dont undertsand why "Run time error '1004' the sort reference is not valid. MAke sure that it is within the daya you want to sort, and then first Sort by Box isnt the same or blank. when I click debug. The .apply gets higlighted

有什么建议吗?

推荐答案

这里只是重申了吉普车的答案，但略有不同:

Just reiterating Jeeped's answer here but with a slightly different take:

1)myline应该真正定义为Long或Integer

1) myline should really be defined as a Long or Integer

2)用Key:=Range("A1")声明的范围应定义为同一工作表

2) The ranges declared with Key:=Range("A1") should be defined as the same worksheet

3)D和J的键在.setRange之外，同样应将其定义为在同一工作表上

3) The keys for D and J are outside of the .setRange which again, should be defined as being on the same worksheet also

我坚持使用相同的代码，但将ws工作表定义添加到所有范围，并更改了设置范围，以包括最多列J

I've stuck with your same code but added the ws worksheet definition to all ranges, and changed your set range to include up to column J

Sub SortMultipleColumns(myline As Long)

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Result-Inactive")

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("A1"), Order:=xlAscending
        .SortFields.Add Key:=ws.Range("D1"), Order:=xlAscending
        .SortFields.Add Key:=ws.Range("J1"), Order:=xlAscending
        .SetRange ws.Range("A1:J" & myline)
        .Header = xlYes
        .Apply
    End With

End Sub

我假设存在myline，因为有时您只想对一系列数据的前一组进行排序.我还为clear所有排序字段添加了一行，以防万一您在此工作表上运行许多不同的排序器.

I'm assuming myline is there because sometimes you only want to sort the top set of a range of data. I also added a line to clear all the sortfields, just in case you run many different sorters on this sheet.

这篇关于排序多列excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！