vba Range类的VBA排序方法失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40917356/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Sort method of Range class failed
提问by anonymous
I know there are several threads on this topic but none of the answers have helped resolved this error.
我知道关于这个主题有几个主题，但没有一个答案有助于解决这个错误。
I am trying to sort a table using three keys but receive the error, "Run-time error '1004': Sort method of Range class failed"
我正在尝试使用三个键对表进行排序，但收到错误“运行时错误‘1004’：Range 类的排序方法失败”
I've tried changing "Range("L2")" to ".Range("L2")" and received the error, "Compile error: Invalid or unqualified reference"
我尝试将“Range(“L2”)”更改为“.Range(“L2”)”并收到错误消息“编译错误：无效或不合格的引用”
I've tried specifying the actual range instead of using columns, I've removed the last two keys and tried just the first, still received the run-time error.
我尝试指定实际范围而不是使用列，我删除了最后两个键并只尝试了第一个，但仍然收到运行时错误。
shtData.Activate
shtData.Range(Range("A2"), Range("Z8000").End(xlUp)).Sort _
Key1:=Range("L2"), Order1:=xlAscending, _
Key2:=Range("M2"), Order2:=xlAscending, _
Key3:=Range("B2"), Order3:=xlAscending, _
Header:=xlYes
If you have any suggestions, I'd greatly appreciate it. I had this working yesterday and my excel crashed and did not recover the changes I made, I cannot figure out why I can't get it to work today.
如果您有任何建议，我将不胜感激。我昨天有这个工作，我的 excel 崩溃了，没有恢复我所做的更改，我不知道为什么我今天不能让它工作。
回答by user3598756
you most probably have no data in "Z" column
您很可能在“Z”列中没有数据
if data rows range can be safely sized by column "A" not empty cells then go like follows
如果数据行范围可以按列“A”而不是空单元格安全地调整大小，则如下所示
Option Explicit
Sub main()
Dim shtData As Worksheet
Set shtData = Worksheets("Data") '<--| some setting of 'shtData'
With shtData
.Range("Z2", .Cells(.Rows.Count, "A").End(xlUp)).Sort _
Key1:=.Range("L2"), Order1:=xlAscending, _
Key2:=.Range("M2"), Order2:=xlAscending, _
Key3:=.Range("B2"), Order3:=xlAscending, _
Header:=xlYes
End With
End Sub
回答by Wedge
If it is possible you could end up changing amount of columns again in the future, you could do something like.
如果有可能您将来再次更改列数，您可以执行类似的操作。
With shtData
Range(Range("A2"), Cells(Range("A8000").End(xlUp).Row, Range("ZZ2").End(xlLeft).Column)
That way it will automatically size the sort area for however many columns you are using as well as rows.
这样，它会自动为您使用的列和行调整排序区域的大小。
