Sorting problem

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990
TEST.xls
ABCDEFGH
1EMPLOYEE NAMETITLEWEEKTEMPREGOTDT
2RATEHOURSHOURSHOURS
3Zb1ZbMANAGERWEEK1exempt
4Zb29915056075WEEK2exempt
5Zb3Hire Date:1/12/00VAC Rate
6Zb4Years of Service:2SIC/Per
7Zb5OFFICE OF YEAR RATE
8Zb6CELEBRATE HRS RATE
9Zb7HOLIDAY PAY RATE
10Zb8
11Zc1ZcFOREMANWEEK1$0.00
12Zc2100138056198WEEK2$0.00
13Zc3Hire Date:7/13/06VAC Rate 
14Zc4Years of Service:5SIC/Per 
15Zc5OFFICE OF YEAR RATE 
16Zc6CELEBRATE HRS RATE 
17Zc7HOLIDAY PAY RATE 
18Zc8
19Za1ZaFOREMANWEEK1$0.00
20Za2123456056198WEEK2$0.00
21Za3Hire Date:8/1/06VAC Rate 
22Za4Years of Service:1SIC/Per 
23OFFICE OF YEAR RATE 
24CELEBRATE HRS RATE 
25HOLIDAY PAY RATE 
Sheet1


Code:
Public Sub GroupSort()
'On Error GoTo TheEnd:
Application.ScreenUpdating = False

msg = "Indicate Row to Start Sort On"
StartRow = Application.InputBox(msg, "First Sort Row", 8, Type:=1)
If StartRow = False Then Exit Sub

' ADD A SORT COLUMN
 Columns("A:A").Insert Shift:=xlToRight
 
 ' POPULATE SORT COLUMN
 Call PopulateSortColumn(StartRow)

' SORT BY SORT COLUMN
With Range("A" & StartRow & ":S" & Cells(65536, 2).End(xlUp).Row + 1)
    .Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

' REMOVE TEMP SORT COLUMN
Columns("A:A").Delete Shift:=xlToLeft

TheEnd:
Application.ScreenUpdating = True
ActiveSheet.Protect "national"

End Sub

Private Sub PopulateSortColumn(StartRow)

For rw = StartRow To Cells(65536, 2).End(xlUp).Row + 1

    'DETERMINE IF NEW START OF NEW EMPL GROUP
    Empl = Trim(Cells(rw, 2).Value)
    On Error Resume Next
    Title = Trim(Cells(rw, 3).Value)
    If Len(Empl) > 0 And Len(Title) > 0 And _
    Not IsNumeric(Empl) And Not IsNumeric(Title) And _
    Not (IsDate(Title)) Then
    
        CurrEmpl = Empl
        SortNum = 1
    End If
    
    'ADD EMPL NAME TO EACH OF THEIR ROWS WITH A SORT NUMBER
    Cells(rw, 1).Value = CurrEmpl & SortNum
    
    ' Increment SortNum
    SortNum = SortNum + 1

Next rw
End Sub

Happy holiday to all....

I was playing around with expanding my excel form and added a a few other items to have sorted. I added to be included in the sort were Office of the Year, Celebrate Hrs Rate, and Holiday Pay Rate. The code is excuting but is not identifing(including) the last 3 cells(A23:A25) so when the sorting takes place its all out of position.

Any idea on how to tackle this, been on it all weekend.

Thanks!!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Bruno

Active Member
Joined
Feb 17, 2002
Messages
491
Code:
With Range("A" & StartRow & ":S" & Cells(65536, 2).End(xlUp).Row + 1) 
    .Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
You use column B to search the last row that is used, right ? Cells(65536, 2).End(xlUp)
But before this you insert a new column to sort the list. So column B is now C. If you use column C instead of column B, I think you problem is solved... (not really tried)
 

Forum statistics

Threads
1,141,019
Messages
5,703,774
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top