Duplicate numbers

amna77

Active Member
Joined
May 9, 2002
Messages
251
Hi in my sheet1 I have so much data. In my column D I have part numbers. The problem is they are duplicate some of them.
Can some one help me creating macro, so I can cut and paste whole row which has duplciate part number to new sheet.

Thanks in advance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
You might want to check out built-in features such as XL's sort (Data | Sort...) and filter (Data | Filter) capabilities. The filter feature has an auto-filter and an advanced filter.
 

amna77

Active Member
Joined
May 9, 2002
Messages
251
I have this macro to find duplcaite numbers in the column. what does it do, it delete the entrire duplciate row. but some how I want to save that entrie row to new blank sheet. is it possible?
Thanks

Sub Duplicate()
Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column
'Columns("A:A").Select
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
Turn on the macro recorder, select a row and copy-paste it elsewhere. Then, modify your code, specifically, the Rng.Rows(r).EntireRow.Delete statement, to include the copy-paste part before the delete.
 

Forum statistics

Threads
1,144,221
Messages
5,723,115
Members
422,478
Latest member
Rovan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top