Sorting Problem

[Connie]

I have a command button to sort the data below. I would like to sort
the data first by TechNo in ascending order for employees that have a
TechNo, and then by name in ascending order for employees who don't
have a TechNo. When I sort by Key1=TechNo, Ascending,
Key2=EmployeeName, Ascending, the employees with no TechNo show up
first. Note that Tech_No is a text field with leading zeros.
Regardless of whether I choose to sort text as numbers or sort normal,
I get the same result. Any suggestions would be appreciated! Thanks

Following is the code I'm using to sort:

' Sort range
Sheets("Compiled Totals").Select
Set rng = GetRealLastCell(ActiveSheet)
Sheets("Compiled Totals").Range("$A$9:" + rng.Address).sort
Key1:=Sheets("Compiled Totals").Range("D9"), Order1:=xlAscending,
Key2:=Sheets("Compiled Totals").Range( _
"B9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal

********************************************************************************************************
Function GetRealLastCell(sh As Worksheet) As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns,
xlPrevious).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function

*****************************************************************************
Data:

EndDate EmployeeName OracleID TechNo
9/30/2006 Tim Jones 12345 1234
10/1/2006 Tim Jones 12345 1234
10/2/2006 Tim Jones 12345 1234
10/3/2006 Tim Jones 12345 1234
10/4/2006 Tim Jones 12345 1234
10/5/2006 Tim Jones 12345 1234
10/6/2006 Tim Jones 12345 1234
9/30/2006 Jan Clark 34567 0
10/1/2006 Jan Clark 34567 0
10/2/2006 Jan Clark 34567 0
10/3/2006 Jan Clark 34567 0
10/4/2006 Jan Clark 34567 0
10/5/2006 Jan Clark 34567 0
10/6/2006 Jan Clark 34567 0
9/30/2006 Joe Hall 34566 2345
10/1/2006 Joe Hall 34566 2345
10/2/2006 Joe Hall 34566 2345
10/3/2006 Joe Hall 34566 2345
10/4/2006 Joe Hall 34566 2345
10/5/2006 Joe Hall 34566 2345
10/6/2006 Joe Hall 34566 2345
9/30/2006 Sally Smith 12345 9876
10/1/2006 Sally Smith 12345 9876
10/2/2006 Sally Smith 12345 9876
10/3/2006 Sally Smith 12345 9876
10/4/2006 Sally Smith 12345 9876
10/5/2006 Sally Smith 12345 9876
10/6/2006 Sally Smith 12345 9876
9/30/2006 John Doe 12345 0654
10/1/2006 John Doe 12345 0654
10/2/2006 John Doe 12345 0654
10/3/2006 John Doe 12345 0654
10/4/2006 John Doe 12345 0654
10/5/2006 John Doe 12345 0654
10/6/2006 John Doe 12345 0654
9/30/2006 Tom Hanks 12345 0345
10/1/2006 Tom Hanks 12345 0345
10/2/2006 Tom Hanks 12345 0345
10/3/2006 Tom Hanks 12345 0345
10/4/2006 Tom Hanks 12345 0345
10/5/2006 Tom Hanks 12345 0345
10/6/2006 Tom Hanks 12345 0345
9/30/2006 Cheryl Ladd 12345 0
10/1/2006 Cheryl Ladd 12345 0
10/2/2006 Cheryl Ladd 12345 0
10/3/2006 Cheryl Ladd 12345 0
10/4/2006 Cheryl Ladd 12345 0
10/5/2006 Cheryl Ladd 12345 0
10/6/2006 Cheryl Ladd 12345 0

 

[Dave Peterson]

Where does your data start?

You start sorting with A9. It looks like you want to start with row 1.

And a couple of side notes:
1. + is use to add numbers.
& is used to concatenate strings

2. Sometimes using the With/end with makes the code easier to understand later:

Option Explicit
Sub test()

Dim rng As Range
Dim wks As Worksheet

Set wks = Worksheets("Compiled totals")
Set rng = GetRealLastCell(wks)

With wks
.Range("$A$1:" & rng.Address).Sort _
Key1:=.Range("D1"), Order1:=xlAscending, _
Key2:=.Range("B1"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End With

End Sub
'************************************
Function GetRealLastCell(sh As Worksheet) As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns, xlPrevious).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function