'Jacek Jarecki Air Freight Referrals'
'tested on vba 2007 excell'

Sub AirFreightReferrals()


' sort Macro '

    Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add Key:=Range("J2:J669") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add Key:=Range("G2:G669") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").sort
        .SetRange Range("A1:N669")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' end sorting '

    Range("A:D,I:I,F:F,M:N").Delete                     'delete columns'
    
    Columns(6).Cut Range("G1")                          'cut column 6(F) and pase in position G(column 7)'
    Columns(2).Cut Range("F1")                          'cut column B and paste in empty column F'
    Columns(2).Delete Shift:=xlToLeft                   'delete the empty column B(2) and move everything left'
    
    Columns("D").HorizontalAlignment = xlHAlignRight    'align column 4(or "D")'
    Columns("F").HorizontalAlignment = xlHAlignLeft     'align "f"'
    
    Columns("D").ColumnWidth = 35                       'set the width of column'
    Columns("E").ColumnWidth = 12                       'set the width of column'
    
    
    
    Dim iRow As Integer                                 'set the variable iRow'
    Range("a1").Select
    iRow = 1
    
    Do
    
        If Cells(iRow + 1, 1) <> Cells(iRow, 1) Then
        
            'insert row'
            Cells(iRow + 1, 1).EntireRow.Insert Shift:=xlDown
            Cells(iRow + 1, 1).Range("A1").RowHeight = 4
            
            'create lines (using loop)'
            
            Dim index As Integer
            index = 1
            
            Do While index <= 6
            
                Cells(iRow + 1, index).Interior.ColorIndex = 15
                index = index + 1
            
            Loop
                                   
            iRow = iRow + 2
            
        Else
        
            iRow = iRow + 1
            
        End If
    
    Loop While Not Cells(iRow, 1).Text = ""
        
End Sub