问题描述:

I am trying to sort data in Excel VBA. My sheet could have a variable amount of data so I am using the Range.End method to find the last row/ column of the sheet. Though, when I try to set the range for the sort it gives me an Object Required error and I am not sure why. I have pasted my code below. Any help would be greatly appreciated.

With Sheet9.Sort

.SetRange (Sheet9.Range("A1").CurrentRegion)

.SortFields.Clear

.SortFields.Add Key:=rng2, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

网友答案:

EDITED BASED ON COMMENTS:

I entered the below code into a mock spreadsheet that I created and it seems to work fine. The error described in the comments: Sort Reference is not valid indicates that there's possibly an issue in defining the key. Try to use my code (replace with necessary information) and let me know if it works.

Sub Doit()

    Set rng2 = Sheet1.Range("A1:A5")

    With Sheet1.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rng2, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Sheet1.Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Based on the ability to use .End for both rows and columns, I assume you have a contiguous data set. If so, instead of complicating matters, simply use:

Range("A1").CurrentRegion to set your range object.

This recognizes the entire dataset that is built around Range("A1")

相关阅读:
Top