gmaxey
06-28-2012, 04:39 AM
I am a novice with Excel programing so about all I can do is trial and error.
I have a function that is intened to sort a worksheet before I populate a listbox.
In an earlier project I discovered how to avoid trying to determine the scope (or range) of my data using rudimentary variables LastRow and LastColumn with the .UsedRange method as I've done in the code below.
Now I need to learn how to modify the "Key" statement to sort on the first column of data and eliminate the need for determining LastRow.
... or if LastRow and LastCol are really required, is there a better way to determing one or both.
Thanks
Function SortData(xlSheet As Object)
LastCol = xlSheet.Range("A1").End(-4161).column
LastRow = xlSheet.Cells(xlSheet.Rows.count, 1).End(-4162).row
With xlSheet.Sort
.SortFields.Clear
'.SetRange xlSheet.Range("A1", xlSheet.Cells(LastRow, LastCol))
.SetRange xlSheet.UsedRange
.SortFields.Add _
Key:=xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(LastRow, 1)), _
SortOn:=0, _
Order:=1, _
DataOption:=0
.Header = 1
.MatchCase = False
.Orientation = 1
.SortMethod = 1
.Apply
End With
End Function
I have a function that is intened to sort a worksheet before I populate a listbox.
In an earlier project I discovered how to avoid trying to determine the scope (or range) of my data using rudimentary variables LastRow and LastColumn with the .UsedRange method as I've done in the code below.
Now I need to learn how to modify the "Key" statement to sort on the first column of data and eliminate the need for determining LastRow.
... or if LastRow and LastCol are really required, is there a better way to determing one or both.
Thanks
Function SortData(xlSheet As Object)
LastCol = xlSheet.Range("A1").End(-4161).column
LastRow = xlSheet.Cells(xlSheet.Rows.count, 1).End(-4162).row
With xlSheet.Sort
.SortFields.Clear
'.SetRange xlSheet.Range("A1", xlSheet.Cells(LastRow, LastCol))
.SetRange xlSheet.UsedRange
.SortFields.Add _
Key:=xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(LastRow, 1)), _
SortOn:=0, _
Order:=1, _
DataOption:=0
.Header = 1
.MatchCase = False
.Orientation = 1
.SortMethod = 1
.Apply
End With
End Function