To create a button that runs an Excel-VBA code to display duplicate values in a column, copy the entire row of the duplicate to Sheet2, and then delete the row from Sheet1, follow these steps:
Open the VBA Editor and Insert the Code:
Open your Excel workbook.
Press Alt + F11 to open the VBA editor.
Insert a new module by clicking Insert > Module.
Copy and paste the following VBA code into the module:
Sub MoveAndDeleteDuplicates()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Dim cell As Range
Dim lastRow1 As Long
Dim lastRow2 As Long
Dim i As Long
' Set the worksheets
Set ws1 = ThisWorkbook.Sheets("Sheet1") ' Change Sheet1 to your source sheet name
Set ws2 = ThisWorkbook.Sheets("Sheet2") ' Change Sheet2 to your destination sheet name
' Determine the last rows in Sheet1 and Sheet2
lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
' Set the range to check for duplicates in Sheet1
Set rng = ws1.Range("A1:A" & lastRow1)
' Loop through each cell in the range to find duplicates
For i = lastRow1 To 1 Step -1
Set cell = ws1.Cells(i, 1)
If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 And cell.Value <> "" Then
' Copy the entire row to Sheet2
ws1.Rows(i).Copy Destination:=ws2.Rows(lastRow2)
lastRow2 = lastRow2 + 1
' Delete the row from Sheet1
ws1.Rows(i).Delete
End If
Next i
' Notify user
MsgBox "Duplicate rows have been moved to Sheet2 and deleted from Sheet1.", vbInformation
End SubInsert a Button in Excel:
Close the VBA editor by clicking the X button or pressing Alt + Q.
Go to the Developer tab in the Excel ribbon. If you don’t see the Developer tab, enable it by going to File > Options > Customize Ribbon, and then check the Developer option.
In the Developer tab, click Insert in the Controls group.
Select the Button (Form Control) from the Form Controls section.
Click and drag on your worksheet to draw the button.
Assign the Macro to the Button:
After drawing the button, the Assign Macro dialog box will appear.
Select MoveAndDeleteDuplicates from the list and click OK.
Rename the Button (Optional):
Right-click the button and choose Edit Text.
Enter the text you want, such as "Move Duplicates".
Save and Test:
Save your workbook.
Click the button you just created to run the macro.
Now, when you click the button, it will execute the MoveAndDeleteDuplicates macro, which will highlight duplicate values in the specified column, copy the entire row of the duplicate to Sheet2, and then delete the row from Sheet1.