PDA

View Full Version : Solved: Sorting



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

Bob Phillips
06-28-2012, 05:28 AM
Is this what you need Greg?

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


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

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

gmaxey
06-28-2012, 02:50 PM
xld,

Thanks. That works perfectly.