Sub macro()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''Date: 11/5/10'''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''Macro looks at a data set and groups similar data together''''''''''
''''''-Deletes empty rows-''''''''''''''''''''''''''''''''''''''''''''''''
''''''-Converts numbers stored as text to numbers-''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim r As Long
Dim i As Integer
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer
''''''Delete empty rows'''''''''
For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next r
'''''Put job codes to numbers''''''
Range("b2").Select
Range(Selection, Selection.End(xlDown)).Select
For Each xcell In Selection
xcell.Value = CDec(xcell.Value)
Next xcell
''''''Group by job number''''''
Range("b2").Select
Selection.End(xlDown).Select
b = ActiveCell.Row - 2
Range("b2").Select
For a = 2 To b
Cells(a, 2).Select
e = ActiveCell.Value
Selection.Offset(1, 0).Select
c = ActiveCell.Row
Selection.End(xlDown).Select
d = ActiveCell.Row
Cells(c, 2).Select
For c = ActiveCell.Row To d
Cells(c, 2).Select
If ActiveCell.Value = e Then
Selection.EntireRow.Copy
Cells(a, 2).Select
Selection.Offset(1, 0).Select
Selection.EntireRow.Select
Selection.Insert Shift:=xlUp
Cells(c, 2).Select
Selection.Offset(1, 0).Select
Selection.EntireRow.Delete
End If
Next c
Next a
Range("a1").Select
''''''Sort rows by column 1''''''
Columns("A:H").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A2:H42")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub