Solved: Sorting

[gmaxey]

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


[vba]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[/vba]

[Bob Phillips]

Is this what you need Greg?

[VBA]Function SortData(xlSheet As Object)
Dim lastcol As Long
Dim lastrow As Long

With xlSheet

lastcol = .Range("A1").End(xlToLeft).Column
lastrow = .Cells(xlSheet.Rows.Count, 1).End(xlUp).Row

.Sort.SortFields.Clear
.Sort.SetRange .UsedRange
.Sort.SortFields.Add Key:=.UsedRange.Columns(1), _
SortOn:=xlSortValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With .Sort

.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Function
[/VBA]

If you are using this code is some other app, you might need this as well

[VBA]Public Enum ExcelConstants
xlYes = 1
xlTopToBottom = 1
xlPinYin = 1
xlUp = -4162
xlToLeft = -4161
xlSortOnValues = 0
xlAscending = 1
xlSortNormal = 0
End Enum[/VBA]

[gmaxey]

xld,

Thanks. That works perfectly.