Sort Arrange Move Rows

[tome10]

Is it possible to move entire rows (A:I at least) up or down based on either date cell or a status cell (If Statement).
I don't know if I explained that very well.
I have a Toggle Button that Toggles between Edit state and Operational State.
Edit state: I edit everything not worrying about sorting the dates Sequentially.
Operational State triggers the Macro that sorts the Rows based on the Date nearest date being Row1, then Row2 Etc Etc. Leapfrog? ;-) Man it's Hard Work being Lazy..

[tome10]

Say I put a bunch of Dates in a Column Randomly. Would this maybe work to arrange them in descending order? When it inserts the cell it should shift cells down.

 Sub ProjSort()

    Dim dCell As Range

        For Each dCell In Sheets("LD").Range("B6:B82")
        If dCell.Offset(1, 0) <> "" Then
        Select Case DateDiff("d", Date, dCell.Value)
            
'            Case Is < dCell:   ??  'Cut and Paste to dCell.Offset(2, 0)

        End Select
        End If
      

    Next
End Sub

[westconn1]

why not just sort the entire range by whichever column

try recording a macro

[tome10]

I made this.. It doesn't error, but it isn't doing anything either..

 Sub ProjSort()

    Dim dCell As Range

        For Each dCell In Sheets("LD").Range("B6:B82")
        If dCell.Offset(1, 0) <> "" Then
        Select Case DateDiff("d", Date, dCell.Value)
            
            Case Is > dCell:    Range(dCell.Offset(1, 0)).Select
                    Application.CutCopyMode = xlCut
                    Range(dCell.Offset(2, 0)).Select
                    Selection.Insert Shift:=xlDown
                    Application.CutCopyMode = False
        End Select
        End If
      
    Next
    
End Sub

[westconn1]

i have not tested the code you are using, but it seems to compare the datediff with itself, as numberofdays compare to date, so is unlikley to do what you expect

[tome10]

Hmm. Date Diff is comparing the Dcell with the Date. Not the date of Dcell and Dcell offset.

 Sub ProjSort()

    Dim dCell As Range

        For Each dCell In Sheets("LD").Range("B6:B82")
        If dCell.Offset(1, 0) <> "" Then
        
        Select Case DateDiff("d", Date, dCell.Offset(1, 0)).Value ' It errors here. No Object.
            Case Is < dCell:    Range(dCell.Offset(1, 0)).Select
                    Application.CutCopyMode = xlCut
                    Range(dCell).Select
                    Selection.Insert Shift:=xlDown
                    Application.CutCopyMode = False
        End Select
        End If
      
    Next
    
End Sub

[tome10]

Hoabout this?

    Private Sub ProjSort()
        
        Dim dCell As Range
        For Each dCell In Sheets("LD").Range("B6:B82")
        If dCell.Offset(1, 0) <> "" And dCell.Offset(1, 0) < dCell Then
            Range(dCell.Offset(1, 0)).Select  'errors here Global Fails
                    Application.CutCopyMode = xlCut
                    Range(dCell).Select
                    Selection.Insert Shift:=xlDown
                    Application.CutCopyMode = False
            
        End If
        
        Next
        
    End Sub

[tome10]

I found this. Anyone know how to use it?

DateValue()

Date part of argument (excellent for ordering by date)
Example: SELECT * from tblPeople ORDER BY DateValue(Review)

Nevermind apparently it's a Function.

[tome10]

This is working somewhat.. I need it to shift cells down when it inserts. ??

    Sub ProjSort()
        Dim rng As Range
        'Dim intRow As Integer, intCol As Integer
        For Each rng In Sheets("LD").Range("B6:B82")
        If Not IsEmpty(rng) Then
            If DateValue(rng.Value) > rng.Offset(1, 0) Then
                rng.Offset(1, 0).Cut Destination:=rng
                Selection.Insert Shift:=xlDown
                Application.CutCopyMode = False
         End If
      End If
   Next rng
End Sub

[tome10]

I found it. Too simple.. I didn't realize Excel had a built in Sort. Never used it.

    Range("A6:I82").Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

[westconn1]

i suggested that in post #3

[tome10]

haha.. That's what I thought I was doing... I was Recording a macro of me (Manually) sorting the rows by the date column. I didn't know the Data-Sort command was there. ;-)

[tome10]

Pete, How do I make this only fill in the link only if RC[4] has someting there?

        For i = 6 To 82

        With Cells(i, 1)
        .FormulaR1C1 = "=HYPERLINK(""http://eic.mycom.com/eic_drw/qr/"" &RC[4],RC[4])"
        With .Font
        .Bold = True
        .Name = "Arial"
        .Size = 12
        .Underline = xlUnderlineStyleSingle
        .ColorIndex = 5
        End With
        End With
        Next

[tome10]

Nevermind Resolved
http://www.vbforums.com/showthread.php?t=592317