Excel macro formula help

Martin01386 OK, I have made changes to:

• correct the error you encountered,
• clear the different set of columns you requested,
• include more comments,
• include prompts on whether the code is making the right initial assumptions about the worksheet to modify and its top row of data, and
• correct an omission.  The earlier version of the code would make, e.g., 4 copies of a row with X in column C if it found 5 rows containing X (whatever X would be).  This newer code makes a single copy, regardless of how many instances it finds.

Sub Martin()
'   A response to this post:
'       https://techcommunity.microsoft.com/t5/excel/excel-macro-formula-help/m-p/3665952

    '   Variables for values/objects that are determined once:
    Dim objWorksheet    As Worksheet
    Dim in4FirstDataRow     As Long
    Dim in4InitialLastRow   As Long
    '   Variables for interaction with the user:
    Dim strMessage      As String
    Dim in4UserResponse As VbMsgBoxResult
    '   Variables for values that change for each row that is
    '   under examination:
    Dim in4Row  As Long
    Dim strRow  As String
    Dim strColCValue    As String
    Dim strFormula      As String
    Dim in4MatchRelRow  As Long
    '   Variables for values that may or may not change for each row:
    Dim in4RowToPossiblyCopy    As Long
    Dim in4RowToCopy    As Long
    Dim strRowToCopy    As String
    Dim in4NewLastRow   As Long
    Dim strNewLastRow   As String
    
    '----   Identify the worksheet that will be modified.  Get user
    '       confirmation.
    Set objWorksheet = ActiveSheet
    '
    strMessage = "This Excel procedure is ready to modify worksheet " _
            & objWorksheet.Name & vbCrLf & vbCrLf _
            & "Click Cancel if that is not the correct worksheet " _
            & ", and rerun this procedure AFTER you make the desired" _
            & " worksheet active."
    in4UserResponse = MsgBox(strMessage, vbExclamation Or vbOKCancel _
            Or vbDefaultButton2)
    If in4UserResponse = vbCancel Then Exit Sub
    
    '----   Determine the first and last rows to work with.  Again
    '       get user confirmation.
    With objWorksheet
        in4FirstDataRow = 2 '<<< You may need to change this value, _
                or include code to calculate it, or prompt for it, _
                depending on how your worksheet content is structured.
        in4InitialLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        '  --   Get user confirmation.
        strMessage = "This procedure may modify data starting in row " _
                & CStr(in4FirstDataRow) & " and add rows starting after row " _
                & CStr(in4InitialLastRow) & vbCrLf & vbCrLf _
                & "Click Cancel if that range of rows is not correct."
        in4UserResponse = MsgBox(strMessage, vbExclamation Or vbOKCancel _
                Or vbDefaultButton2)
        If in4UserResponse = vbCancel Then Exit Sub
    End With
        
    '----   Other preparation:
    in4NewLastRow = in4InitialLastRow
        
    '----   The main processing:
    With objWorksheet
        For in4Row = in4FirstDataRow To in4InitialLastRow - 1
            strRow = CStr(in4Row)
            '   Pick up the column C value in a row.
            strColCValue = .Range("C" & strRow).Value
            '   Find any "duplicate" in the rows below it.
            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
            
            '   Find any "duplicate" in the NEW rows.  If found there,
            '   the proper row was copied while examining an earlier
            '   row with this duplicated value.
            strFormula = "MATCH(""" _
                    & Replace(strColCValue, """", """""") _
                    & """, C" & CStr(in4InitialLastRow + 1) _
                    & ":C" & CStr(in4NewLastRow) _
                    & ",0)"
            in4MatchRelRow = 0
            On Error Resume Next
            in4MatchRelRow = .Evaluate(strFormula)
            On Error GoTo 0
            If in4MatchRelRow > 0 Then
                '...the duplicated value already has a new row.
                GoTo ClearColumnsInThisRow
            End If
            
            '...We've found at least one "duplicate".  Loop to
            '   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.
            strRowToCopy = CStr(in4RowToCopy)
            in4NewLastRow = in4NewLastRow + 1
            strNewLastRow = CStr(in4NewLastRow)
            .Range("A" & strRowToCopy).EntireRow.Copy Destination:= _
                    .Range("A" & strNewLastRow)
            
            '  --   Clear/change certain columns in the new row.
            .Range("D" & strNewLastRow & ":G" & strNewLastRow) _
                    .ClearContents
            .Range("H" & strNewLastRow).Value = _
                    .Range("C" & strRowToCopy).Value
            
ClearColumnsInThisRow:
            '  --   As this current row is NOT the last row (in the
            '       original range of rows) that has the "duplicate"
            '       column C value*, clear certain columns in it.
            '   * If it had been the last such "duplicate" row, the
            '     upper MATCH would not have found any "duplicate"
            '     beneath it.
            .Range("D" & strRow).ClearContents
            .Range("AC" & strRow & ":AD" & strRow).ClearContents
            .Range("AG" & strRow).ClearContents
NextRowToExamine:
        Next in4Row
    End With

End Sub

I am also attaching the workbook I used for testing, now with an extra row of test data.  Worksheet Martin2 is initially an exact copy of worksheet Martin01386, and Martin2 is the one I have been modifying (and refreshing) during the testing.