Excel macro formula help

[Martin01386]

Hi Guys I am not an expert on Excel and searching for a solution. I have a sheet with lots of data and some of have to change in order. 

The macro I am looking for is like this...

If multiple rows have the same value in "column C" 

• Copy last whole row 1 time 
• And delete cells "D,E,F,G" of that new row 
• And Copy and paste value of cell "C" To "H" of that new row 

And If multiple rows have the same value in "column C" 

• delete cells "I,J,K,L" for each row but not the last

Hopefully someone can help me. Thanks a lot :)

[#2 · Accepted solution]

@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.

[#3]

Thank you @SnowMan55 that is exactly what I mean!

When I try to run this maco I get the following error:

error 1004 during execution:

ClearContents method of Range class failed

The following line gets highlighted

.Range("A" & strRowToCopy).EntireRow.Copy _
(.Range("A" & strNewLastRow))

What am i doing wrong? 

[#4]

Also the actual cells to clear from rows with the same value in column C except the last row are D, AD, AC, AG (instead of I:L)

[#5]

@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.

[#6]

Thank you snowman55!

you are a hero.

Code works perfect now.

Only thing I experience now for a large file the macro needs quite some time to run as it process each row now. Maybe my question was beter like "as column C has the same value, copy one row to a new tab" + "clear columns .... in orginal tab and clear columns ... in new tab" and then "copy second tab at the end of original tab".

But since it works my problem is solved. Thank you so much