Excel macro formula help

Martin01386 

Some of your request is ambiguous.

1. Where you wrote "Copy last row...", I'm going to assume you mean "Copy the last row among the 'duplicate column C value' rows..."
2. Where you wrote "...delete cells...", I'm going to assume you mean "...clear the content of the cells..."
3. Where you wrote "...not the last", I'm going to assume you mean "...not the last of the 'duplicate column C value' rows"

So I believe you want:

...to become:

If I'm correct, consider this procedure (feel free to change the name).  I expect it will work even if column C does not contain text.

Sub Martin()

    Dim objWorksheet    As Worksheet
    Dim in4FirstDataRow     As Long
    Dim in4InitialLastRow   As Long
    Dim in4Row  As Long
    Dim strRow  As String
    Dim in4NewLastRow   As Long
    Dim strNewLastRow   As String
    '
    Dim strColCValue    As String
    Dim strFormula      As String
    Dim in4MatchRelRow  As Long
    Dim in4RowToPossiblyCopy    As Long
    Dim in4RowToCopy    As Long
    Dim strRowToCopy    As String
    
    '----
    Set objWorksheet = ActiveSheet  '<<< If you want changes on the active worksheet, or...
    Set objWorksheet = Sheets("Martin2") '<<< if you want changes on a specific worksheet
    With objWorksheet
        in4FirstDataRow = 2 '<<< You may need to change this value, or _
                include code to calculate it.
        in4InitialLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        in4NewLastRow = in4InitialLastRow
        '
        For in4Row = in4FirstDataRow To in4InitialLastRow - 1
            strColCValue = .Range("C" & CStr(in4Row)).Value
            strFormula = "MATCH(""" _
                    & Replace(strColCValue, """", """""") _
                    & """, C" & CStr(in4Row + 1) _
                    & ":C" & CStr(in4InitialLastRow) _
                    & ",0)"
            in4MatchRelRow = 0
            On Error Resume Next
            in4MatchRelRow = .Evaluate(strFormula)
            On Error GoTo 0
            If in4MatchRelRow = 0 Then
                '...this row has no "duplicate" in column C
                '   among the original rows below it.
                GoTo NextRowToExamine
            End If
            '  --   We've found at least one "duplicate".
            '       Locate the last among these "duplicates".
            For in4RowToPossiblyCopy = in4InitialLastRow To _
                    (in4Row + in4MatchRelRow) Step -1
                If StrComp(.Range("C" & in4RowToPossiblyCopy).Value _
                        , strColCValue, vbTextCompare) = 0 Then
                    '...a match; this is the one to copy.
                    in4RowToCopy = in4RowToPossiblyCopy
                    Exit For
                End If
            Next in4RowToPossiblyCopy
            '  --   Copy that row to the end of the data.
            in4NewLastRow = in4NewLastRow + 1
            strNewLastRow = CStr(in4NewLastRow)
            strRowToCopy = CStr(in4RowToCopy)
            .Range("A" & strRowToCopy).EntireRow.Copy _
                    (.Range("A" & strNewLastRow))
            '  --   Clear/change columns in the new row.
            .Range("D" & strNewLastRow & ":G" & strNewLastRow) _
                    .ClearContents
            .Range("H" & strNewLastRow).Value = _
                    .Range("C" & strRowToCopy).Value
            '  --   As this current row is NOT the last row that has
            '       the same column C value, clear columns in it.
            strRow = CStr(in4Row)
            .Range("I" & strRow & ":L" & strRow).ClearContents
NextRowToExamine:
        Next in4Row
    End With

End Sub

 I have preferred clarity of code over efficiency.