delete dupes in column

[BigRog]

All,
I have a worksheet with < 200 rows of data (6 Cols). I need to eliminate the
entire row of data if Column "B" contains any duplicate entries. I prefer
using VBA, as the rest of the project uses it almost exclusively. I have
tried looping thru the entire table for each new value in the column, but it
takes much too long and results in buffer overruns.

Thanks,
BigRog

 

[macropod]

Hi The following macro shows various options for hiding/deleting rows

Sub HideOrDeleteDuplicates()

Dim A As Long
Dim B As Long
Dim C As Range
Dim D As Range

On Error GoTo Abort
' To test all columns use:
'Set D = ActiveSheet.UsedRange.Columns
'To test a limited range of columns, select them and use:
'Set D = Selection.Columns
' To test all rows use:
'Set C = ActiveSheet.UsedRange.Rows
'To test a limited range of rows, select them and use:
'Set C= Selection.Rows
For B = D.Columns.Count To 1 Step -1
For A = C.Rows.Count To 1 Step -1
' To hide or delete the whole row with offending cells, use:
' If Application.WorksheetFunction.CountIf(Range(Rows(1).Columns(1),
Rows(A).Columns(B)), C.Rows(A).Columns(B)) > 1 Then
' To hide the row, use:
'C.Rows(A).EntireRow.Hidden = True
' To unhide a row whose status has changed, use:
'Else C.Rows(A).EntireRow.Hidden = False
' To delete the row , use:
'C.Rows(A).EntireRow.Delete
' To delete duplicate cells in the same column, use the next two lines:
'If Application.WorksheetFunction.CountIf(Columns(B),
C.Rows(A).Columns(B)) > 1 Then
'Rows(A).Columns(B).Delete Shift:=xlUp
End If
Next A
Next B
' If only hiding for printing purposes, use the next two lines to print or
preview then restore the worksheet
'ActiveWindow.SelectedSheets.PrintPreview
'ActiveSheet.Rows.EntireRow.Hidden = False
Abort:
End Sub

Adapt to suit your needs by uncommenting the lines you want to use. Backup
before using it, just in case you choose the wrong combo.

Cheers

 

[Neil]

Have a look at Chip Pearson's page

http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows

Neil

 

[BigRog]

Thanks Everyone,

I linked to the page and found just what I needed. Great link

BigRog

 

[Sameer Kumar]

Hello Guys
I'm new to this forum as well excel vba.
I read your messages and found useful. In given case you are finding for
duplicates on basis of one column.
It works perfectly fine. But my need is to find the duplicates on the
basis of combination of more than one column.
I got stucked, so neee some powerful advice.
Thanks
Sameer

 

[Frank Kabel]

Hi
one workaround: use a helper column. Lets say you have data in columns
A-C then use column D as helper column and enter the formula
=A1&B1&C1
in cell D1. Copy this formula down for all rows
Now use the procedure described in this thread on column D to identify
duplicates