Excel VBA move row up once status is marked “DONE” above a merged row and put in numerical order.

christopherwhiten 

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module,

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cel As Range
    Dim trg As Range
    Set rng = Intersect(Range("D18:D30"), Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each cel In rng
            If cel.Value = "DONE" Then
                Set trg = Range("A18").End(xlUp)
                If trg.Row = 1 Then
                    MsgBox "No more room!", vbExclamation
                    Exit For
                End If
                cel.EntireRow.Copy Destination:=trg.Offset(1)
                cel.EntireRow.Delete
                Range("A1").CurrentRegion.Sort Key1:=Range("C1"), Header:=xlYes
            End If
        Next cel
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub