使用excel中的自定义顺序进行排序会给出错误1004 [英] Sorting using a custom order in excel gives error 1004

问题描述

我试图通过两列两列列表中的数据进行排序 - 首先是列B（按字母顺序排序），然后是列C（使用自定义顺序G，D，M，F - - 这些是在列中发生的唯一值。但是，当我尝试运行代码时，我收到错误

I'm trying to sort data in a sheet with several columns by two of the columns -- first by column B (alphabetically), then by column C (using the custom order "G, D, M, F" -- these are the only values that occur in the column). However, when I try to run the code, I get the error

1004 - Unable to get the Sort property of the Range class

所以这里是我正在使用的。在代码之前我有

So here's what I'm working with. Earlier in the code I have

Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row

然后这里是我得到错误的部分：

Then here's the part where I get the error:

Range("A2:Y" & lastrow).Sort.SortFields. _
Add Key:=Range("C2:C" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
Range("A2:Y" & lastrow).Sort.SortFields. _
Add Key:=Range("B2:B" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:="G,D,M,F", DataOption:=xlSortNormal

推荐答案

你将不得不添加自定义排序顺序作为数组排列到自定义列表中。排序时，您必须排序两次。一次次要自定义排序顺序，然后在主非自定义键上。

You are going to have to add the custom sort order into the custom lists as an array first. When sorting, you will have to sort twice. Once on the secondary custom sort order and then on the primary non-custom key.

Dim vCOLs As Variant

vCOLs = Array("G", "D", "M", "F")

With Application
    '.ScreenUpdating = False
    '.EnableEvents = False
    .AddCustomList ListArray:=vCOLs
End With

With Worksheets("sheet2")
    .Sort.SortFields.Clear
    With .Cells(1, 1).CurrentRegion
        'first sort on the secondary custom sort on column B
        .Cells.Sort Key1:=.Columns(2), Order1:=xlAscending, _
                    Orientation:=xlTopToBottom, Header:=xlYes, _
                    OrderCustom:=Application.CustomListCount + 1
        'next sort on the primary key; column C
        .Cells.Sort Key1:=.Columns(3), Order1:=xlAscending, _
                    Orientation:=xlTopToBottom, Header:=xlYes

    End With
    .Sort.SortFields.Clear
End With

我不完全确定正在进行你的行1.你的原始代码在第2行开始排序，但不表示是否有一个标题行。

I'm not entirely sure what is going on with your row 1. Your original code starts the sort in row 2 but gives no indication whether you have a header row or not.

这篇关于使用excel中的自定义顺序进行排序会给出错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！