Vba sort list

[ch_abs]

Hi All,

I have a list that looks like:
Capture.PNG

The list starts from column O and can run across to column Z, and the numbers in the list go up to 89.
When I sort the cells in Excel by O, then P, Q...etc then I end up with a list like I have shown above.

This list is to show dependencies of jobs. Therefore, on the row where the dependencies go '1,2,3,4,5,6,7,8,9,10' I'd like to see it sorted so that it comes under the dependency '10'.....because how can I run a job just after job '1', when it dependends on '1,2,3,4,5,6,7,8,9,10' being complete aswell!

Below is the code I have attempted and attached is an example workbook to work with. I have a few MsgBox's just for de-bugging to try and help me figure out the problem..as you can see that didn't go so well! Also, (anyone who helps me - thank you in advance!!!!) save the workbook first...my code eats rows/makes them do a disappearing act...

Option Explicit
Public Last_Row2 As Long

Sub SortRefValues()
Call Find_Last_Row_Main
MsgBox Last_Row2
Call SortSheet
Call Correct_Dep_Order
End Sub
Sub SortSheet()
    Range("A2:Z91").Select
    Range("Z2").Activate
    ActiveWorkbook.Worksheets("Main").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Main").Sort.SortFields.Add Key:=Range("O" & "2" & ":" & "O" & Last_Row2), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Main").Sort
        .SetRange Range("A2:X91")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    Range("O2").Select
End Sub

Sub Correct_Dep_Order()

Dim lastColumn As Long
Dim Found_Address4 As String
Dim Found_Row4 As Long
Dim RowBelow As Range
Dim Row4 As Long
Dim Row5 As Long
Dim ACRow As Range
Dim Last_Cell_Address As Range
Dim Row_Above_Cell_Compare As Range
Dim Row_Below_Cell_Compare As Range
Dim Row_Above As Range
Dim Row_Below As Range
Dim StoreAddress As String
Dim Temp As Range
Dim Cell As Range
Dim lastColumn2 As String
Dim iAlpha As Integer
Dim iRemainder As Integer
Dim Last_Cell_Val As String

MsgBox "In mainy the lastr orw:" & Last_Row2
Set Last_Cell_Address = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft)
MsgBox "last_cell_address " & Last_Cell_Address
Found_Row4 = Right(Last_Cell_Address.Address, Len(Last_Cell_Address.Address) - 3)
MsgBox "Found_Row4 " & Found_Row4
'Row above Active Cell
Row4 = Found_Row4 - 1
MsgBox "Row4 which is the row number above " & Row4
'Row Below Active Cell
Row5 = Found_Row4 + 1
MsgBox "Row5 row below selecgted " & Row5

'Row range of active Cell
'ACRow = Sheets("Main").Range("A" & Found_Row4 & ":Z" & Found_Row4)
Set ACRow = Sheets("Main").Range("A" & Found_Row4 & ":Z" & Found_Row4)
'MsgBox "Row of selected " & ACRow
'Row above active cell and the cell comparing in col O
Set Row_Above_Cell_Compare = Sheets("Main").Range("O" & Row4)
Set Row_Below_Cell_Compare = Sheets("Main").Range("O" & Row5)
'MsgBox "row above, cell comparing " & Row_Above_Cell_Compare
'whole row above Active Cell
Set Row_Above = Sheets("Main").Range("A" & Row4 & ":Z" & Row4)
'MsgBox "whole row above " & Row_Above
'whole row below acive
Set RowBelow = Sheets("Main").Range("A" & Row5 & ":Z" & Row5)
'MsgBox "Whole row below " & RowBelow
Last_Cell_Val = Last_Cell_Address.Value
MsgBox "Last_Cell_Val " & Last_Cell_Val

MsgBox "before for last row " & Last_Row2
'while the value in the last col in active row is greater than the col O in the row above

For Each Cell In Range("O2" & ":" & "O" & Last_Row2)
MsgBox "In For"
    If ((Last_Cell_Address) = "0") Then
    MsgBox "1"
        Found_Row4 = Right(Last_Cell_Address.Address, Len(Last_Cell_Address.Address) - 3)
        MsgBox "2"
        lastColumn = ActiveSheet.Cells(Found_Row4, Columns.Count).End(xlToLeft).Column
        MsgBox "3"
        'iAlpha = Int(lastColumn / 27)
        'iRemainder = lastColumn - (iAlpha * 26)
       ' lastColumn2 = lastColumn2 & Chr(iRemainder + 64)
        
        Last_Cell_Address = Sheets("Main").Range(lastColumn & ":" & Found_Row4)
        MsgBox "4"
        'StoreAddress = Row_Above_Cell_Compare.Address
        Row4 = Found_Row4 - 1
        MsgBox "5"
        Set Row_Above_Cell_Compare = Sheets("Main").Range("O" & Row4)
        MsgBox "6"
        ACRow = Sheets("Main").Range("A" & Found_Row4 & ":Z" & Found_Row4)
        MsgBox "7"
        Row5 = Found_Row4 + 1
        MsgBox "8"
        Set RowBelow = Worksheets("Main").Range("A" & Row5 & ":Z" & Row5)
        MsgBox "9"
        Set Row_Above = Sheets("Main").Range("A" & Row4 & ":Z" & Row4)
        MsgBox "10"
        Set Row_Above_Cell_Compare = Sheets("Main").Range("O" & Row4)
        MsgBox "11"
        Set Row_Below_Cell_Compare = Sheets("Main").Range("O" & Row5)
      MsgBox "12"
    ElseIf (Range("O" & (Row5)) = "0") Then
        Found_Row4 = Right(Last_Cell_Address.Address, Len(Last_Cell_Address.Address) - 3)
        lastColumn = ActiveSheet.Cells(Found_Row4, Columns.Count).End(xlToLeft).Column
       ' iAlpha = Int(lastColumn / 27)
       ' iRemainder = lastColumn - (iAlpha * 26)
       ' lastColumn2 = lastColumn2 & Chr(iRemainder + 64)
        Last_Cell_Address = Sheets("Main").Range(lastColumn & ":" & Found_Row4)
        'StoreAddress = Row_Above_Cell_Compare.Address
        Row4 = Found_Row4 - 1
        Set Row_Above_Cell_Compare = Sheets("Main").Range("O" & Row4)
        ACRow = Sheets("Main").Range("A" & Found_Row4 & ":Z" & Found_Row4)
        Row5 = Found_Row4 + 1
        Set RowBelow = Worksheets("Main").Range("A" & Row5 & ":Z" & Row5)
        Set Row_Above_Cell_Compare = Sheets("Main").Range("O" & Row4)
        Set Row_Below_Cell_Compare = Sheets("Main").Range("O" & Row5)
    'edit below
    ElseIf ((Last_Cell_Address) > (Row_Above_Cell_Compare)) & ((Last_Cell_Address) > (Row_Below_Cell_Compare)) Then
       ' MsgBox "In IF"
       ' MsgBox Last_Cell_Address
       ' Temp = ACRow
        'MsgBox "okay1"
        'ACRow = RowBelow
       ' MsgBox "okaay2"
        'RowBelow = Temp
        'MsgBox "OKay3"
        Range(ACRow).Select
        Range("Z:" & Found_Row4).Activate
        Selection.Cut
        Rows(Row5 & ":" & Row5).Select
        Range("Z" & Row5).Activate
        Selection.Insert Shift:=xlDown
        'StoreAddress = Last_Cell_Address.Address
    ElseIf ((Last_Cell_Address) > (Row_Above_Cell_Compare)) & ((Last_Cell_Address) < (Row_Below_Cell_Compare)) Then
        MsgBox "last cell adress in else" & Last_Cell_Address
        Found_Row4 = Right(Last_Cell_Address.Address, Len(Last_Cell_Address.Address) - 3)
        lastColumn = ActiveSheet.Cells(Found_Row4, Columns.Count).End(xlToLeft).Column
        'iAlpha = Int(lastColumn / 27)
       ' iRemainder = lastColumn - (iAlpha * 26)
       ' lastColumn2 = lastColumn2 & Chr(iRemainder + 64)
        Last_Cell_Address = Columns(lastColumn).Rows(Found_Row4)
       ' StoreAddress = Row_Above_Cell_Compare.Address
        Row4 = Found_Row4 - 1
        Set Row_Above_Cell_Compare = Sheets("Main").Range("O" & Row4)
        ACRow = Sheets("Main").Range("A" & Found_Row4 & ":Z" & Found_Row4)
        Row5 = Found_Row4 + 1
        Set RowBelow = Worksheets("Main").Range("A" & Row5 & ":Z" & Row5)
        Set Row_Above_Cell_Compare = Sheets("Main").Range("O" & Row4)
        Set Row_Below_Cell_Compare = Sheets("Main").Range("O" & Row5)
    End If
Next Cell


End Sub
Function Find_Last_Row_Main()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Main")
With ws
    Last_Row2 = .Range("A" & Rows.Count).End(xlUp).Row
End With
MsgBox Last_Row2

End Function

Thanks in advance, I've been struggling a long time on this!