removing duplicates and deleting the row

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Mar 4, 2009

• #2

A search of the board for "Deleting Duplicates" will turn up Several hundred examples
The most frequently asked question on the board!!
lenze

 

Upvote 0

Z

zibanitum

Board Regular

Joined

Feb 26, 2008

Messages

88

• Mar 4, 2009

• #3

For example, the ID number is in Column F. ID number 7756 appears on F 24. The number 7756 also appears on F 57. I would like row 57 to be deleted, so only one ID number of 7756 would be in the spread sheet.

 

Upvote 0

T

Trebor76

Well-known Member

Joined

Jul 23, 2007

Messages

5,274

Office Version

1. 365

Platform

1. Windows

• Mar 4, 2009

• #4

Hi zibanitum,

Post back with an answer to the following and I'll have a look:

1. What column are the ID's are in,
2. The row number (eg 2) where the dataset starts, and
3. Is it OK to sort the dataset?

HTH

Robert

 

Upvote 0

Z

zibanitum

Board Regular

Joined

Feb 26, 2008

Messages

88

• Mar 5, 2009

• #5

The column is F
It starts in F2
If you mean sort in numerical order or whatever is fine -- as long as all the corresponding information in the other columns still match the ID.

 

Upvote 0

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,300

• Mar 5, 2009

• #6

No need to sort...see if this does what you want, but test on a copy of the workbook.

Deletes duplicates in column F.

Code:

Sub Test1()
With Application
.ScreenUpdating = False
.EnableEvents = False
Dim LC As Integer
LC = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
With Range("F1:F" & Cells(Rows.Count, 6).End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.SpecialCells(12).Offset(0, LC - 6).Value = 1
On Error Resume Next
ActiveSheet.ShowAllData
Columns(LC).SpecialCells(4).EntireRow.Delete
Err.Clear
End With
Columns(LC).Clear
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

 

Upvote 0

Z

zibanitum

Board Regular

Joined

Feb 26, 2008

Messages

88

• Mar 5, 2009

• #7

Thanks, Tom. On my small test data, everything is working fine. I will try it on a large scale tomorrow.

Suppose the data is in a different column. What are the steps I would need to take to adjust the macro?

p.s. I would like to thank everyone for their time and input.

 

Upvote 0

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,300

• Mar 5, 2009

• #8

zibanitum said:

Suppose the data is in a different column. What are the steps I would need to take to adjust the macro?

Click to expand...

Adjust the column references and subtraction of the column number per the below example:

Column F is column 6 which is why the code I posted looks like this:

With Range("F1:F" & Cells(Rows.Count, 6).End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.SpecialCells(12).Offset(0, LC - 6).Value = 1


If you want to do this for column H which is column 8, the code would change as bolded:

With Range("H1:H" & Cells(Rows.Count, 8).End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.SpecialCells(12).Offset(0, LC - 8).Value = 1

 

Upvote 0

Z

zibanitum

Board Regular

Joined

Feb 26, 2008

Messages

88

• Mar 6, 2009

• #9

Everything is working fine. Thanks for all your help.

 

Upvote 0

S

stevenwhickey

New Member

Joined

Apr 4, 2006

Messages

42

• Mar 20, 2009

• #10

Hey there,
How would I modify this to delete it if two columns are the same? For Instance:

1 j
2 h
3 g
4 k
1 j
3 g


In the case above the last two rows would be deleted.

 

Upvote 0