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.
.SortFields.Add Key:=rng2, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
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