Sorting using VBA

 Private Sub Worksheet_Calculate()
'On change to any cell in the range this code executes my copy macro
Dim Xrg As Range
Set Xrg = Worksheets("Tuesday").Range("E4:E53")
If Not Intersect(Xrg, Worksheets("Tuesday").Range("E4:E53")) Is Nothing Then     
sbDriverCopy
End If
Set Xrg = Nothing
End Sub
Private Sub sbDriverCopy()
' This macro copies my data from one range to another and than executes my sort macro
With Worksheets("Tuesday")
Application.ScreenUpdating = False
.Range("I4:M53").ClearContents
.Range("D4:H53").Copy
.Range("I4:M53").PasteSpecial xlPasteValues
.Range("A2").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
sbDriverSort
End With
End Sub
Private Sub sbDriverSort()
'This macro sorts my data range based on the I column
With Worksheets("Tuesday")
Application.ScreenUpdating = False
.Range("I4:M53").Sort key1:=Range("I4:I53"), order1:=xlDescending, Header:=xlNo
Application.Selection = False
Application.ScreenUpdating = True
End With
End Sub

When it sorts I have a few problems: 1. It puts blanks and 0 first. I need those records to stay at the bottom.Those records contain the following values: Blank Blank 0 70 that is four seprate columns

  1. I need my sort to put the person with the least amount of hours worked at the top of the list and go down from there.
  2. So I will have some records with the following values: some number of hours in all four columns: 12 3 12 58 these are the records that i want to sort with these include as well the other record looks something like this blank blank 10 60

    the final record looks like this: Blank Blank 0 70 I need all these records to stay at the bottom.

It is a Driver Tracking Sheet of their hours but some people may not have work that day or any other for that matter. So the sheet tracks hours and puts drivers in order of rotation for the next day based on hours work the previous day.

Add Comment
0 Answer(s)
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.