VBA & Macro Problems

Excel VBA Macro to Process Duplicate Values in Column C

Anum MushtaqAnum Mushtaq Nov 18, 2026 869 views

Question details

Create a VBA macro that processes duplicate values in column C and updates selected cells in the matching rows.

Process Duplicate Values in Excel Column C with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
For each duplicated value in column C, the user wants to copy the last matching row below the existing data and modify specified columns.
Observed behavior
The desired result is one appended row per duplicated value, with the value written to column H and columns I:L cleared in earlier matching rows.
Before you start

Confirm that row 1 contains headers and that the active worksheet is editable, with no merged cells in the affected ranges. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and test the macro on a copy with a few known duplicates.

Solution 1Recommended

Run a VBA macro to process duplicate values in column C

Use this macro in Excel for Windows to append one copy of the last matching row for each duplicated value.

The macro examines rows 2 through the last populated row in column C. It ignores blank and error values, compares text without regard to case, and treats values with the same text representation as matches.

For each duplicate group, it clears the contents of I:L in earlier matching rows. The appended copy has D:G and I:L cleared, and its column C value is written to H. The original last matching row is unchanged. ClearContents removes contents, not formatting or entire cells.

1
Open the VBA editor

In Excel, open the workbook and press Alt+F11. Select Insert > Module in the VBA editor.

2
Paste the macro

Paste this code into the module: Sub ProcessColumnCDuplicates() Dim ws As Worksheet Dim counts As Object, lastMatch As Object Dim lastC As Long, lastUsed As Long, r As Long Dim nextRow As Long, groups As Long Dim key As String, k As Variant Dim found As Range Set ws = ActiveSheet Set counts = CreateObject("Scripting.Dictionary") Set lastMatch = CreateObject("Scripting.Dictionary") counts.CompareMode = vbTextCompare lastMatch.CompareMode = vbTextCompare lastC = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row If lastC < 2 Then Exit Sub Set found = ws.Cells.Find(What:="*", LookIn:=xlFormulas, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If found Is Nothing Then Exit Sub lastUsed = found.Row For r = 2 To lastC If Not IsError(ws.Cells(r, "C").Value) Then key = CStr(ws.Cells(r, "C").Value) If Len(key) > 0 Then If counts.Exists(key) Then counts(key) = counts(key) + 1 lastMatch(key) = r Else counts.Add key, 1 lastMatch.Add key, r End If End If End If Next r For Each k In counts.Keys If counts(k) > 1 Then groups = groups + 1 Next k If groups = 0 Then MsgBox "No duplicates found in column C." Exit Sub End If If groups > ws.Rows.Count - lastUsed Then MsgBox "There are not enough rows below the data." Exit Sub End If nextRow = lastUsed + 1 For r = 2 To lastC If Not IsError(ws.Cells(r, "C").Value) Then key = CStr(ws.Cells(r, "C").Value) If Len(key) > 0 Then If counts(key) > 1 Then If r = lastMatch(key) Then ws.Rows(r).Copy Destination:=ws.Rows(nextRow) ws.Range("D" & nextRow & ":G" & nextRow).ClearContents ws.Range("I" & nextRow & ":L" & nextRow).ClearContents ws.Cells(nextRow, "H").Value = ws.Cells(r, "C").Value nextRow = nextRow + 1 Else ws.Range("I" & r & ":L" & r).ClearContents End If End If End If End If Next r MsgBox groups & " duplicate group(s) processed." End Sub

3
Run the macro

Return to Excel and select the worksheet to process. Press Alt+F8, select ProcessColumnCDuplicates, and click Run.

4
Check the results

Review the appended rows below the last used row and check columns H and I:L. Confirm that clearing cell contents, rather than deleting cells, matches your intended result.

Run a VBA macro to process duplicate values in column C
Confirm the rules before processing: This example assumes one appended row per duplicate group and leaves the original last matching row unchanged. Adjust the rules if your expected result differs. The macro uses Windows Scripting.Dictionary and may not run unchanged in Excel for Mac.
Free Microsoft Office alternative

Review duplicate spreadsheet data with WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel files to review duplicate data and move between office workflows; it does not directly fix or guarantee compatibility with this Excel VBA macro.

Review and edit spreadsheet data, including values in column C.Compatible with Microsoft Excel formats such as XLSX.Familiar interface for an easier transition from Microsoft Office.Free and lightweight for everyday office tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Does the macro process a value that appears only once?

No. It processes only values that appear at least twice in column C, starting at row 2.

What happens to the original last matching row?

It remains unchanged. The macro copies it below the last used row, then clears D:G and I:L and writes the column C value to H in the copy.

Does ClearContents delete cells or their formatting?

No. The macro uses ClearContents to remove cell contents while leaving the cells and their formatting in place.

Where are the copied rows added?

They are appended below the worksheet's last used row, even if another column contains data below the last value in column C.

Why should I save the workbook as an XLSM file?

An Excel Macro-Enabled Workbook preserves VBA code. In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).