eliminate duplicates

guest

Board Regular
Joined
Feb 21, 2002
Messages
91
Hi I have some sequence numbers in coulumn A and i want to eliminate duplicate rows from the sheet. How can i do it any suggestions???
Thanks
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
On 2002-05-10 09:43, guest wrote:
Hi I have some sequence numbers in coulumn A and i want to eliminate duplicate rows from the sheet. How can i do it any suggestions???
Thanks

You can do this by using DATA|FILTER|Advanced_Filter

have the un1que entries copied to another column say, column C, then proceed from there.

Regards!
 
Upvote 0
try this code:

Sub Edit_EmptyDuplicateCells()
On Error GoTo EndMacro
If selection.Columns.Count > 1 Then
MsgBox "Sorry, you may only select cells in one column.", vbExclamation
Exit Sub
Else
End If
If selection.Rows.Count > 1 Then
Set rng = selection
Else
MsgBox "Please select cells in multiple rows.", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
i = selection.Cells.Count
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).ClearContents
n = n + 1
End If
Percentage = n / i * 100
Application.StatusBar = "Now processing: " & Percentage & "%"
Next r
EndMacro:
MsgBox Format(n) & " cells have been cleared.", vbInformation
End Sub
 
Upvote 0
This code will delete dupes until a blank row is found.

Code:
Sub DeleteDups()
  Do
  RowCnt = RowCnt + 1
  RowCnt2 = RowCnt + 1
   Do
        If Cells(RowCnt, 1).Value = Cells(RowCnt2, 1).Value Then Rows(RowCnt2 & ":" & RowCnt2).Delete Shift:=xlUp
      RowCnt2 = RowCnt2 + 1
    Loop Until Len(Trim(Cells(RowCnt2, 1).Value)) = 0
  Loop Until Len(Trim(Cells(RowCnt, 1).Value)) = 0
End Sub
This message was edited by Nimrod on 2002-05-10 11:05
This message was edited by nimrod on 2002-05-10 11:17
This message was edited by Nimrod on 2002-05-10 11:19
This message was edited by Nimrod on 2002-05-10 11:23
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top