delete dupes in column

B

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
 
M

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
 
B

BigRog

Thanks Everyone,

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

BigRog
 
Top