delete dupes in column
 

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

delete dupes in column
 

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


"BigRog" wrote in message
...
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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 5/03/2004

delete dupes in column
 

Have a look at Chip Pearson's page

http://www.cpearson.com/excel/deleti...eDuplicateRows

Neil

"BigRog" wrote in message
...
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

delete dupes in column
 

Thanks Everyone,

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

BigRog
"Neil" wrote in message
...
Have a look at Chip Pearson's page

http://www.cpearson.com/excel/deleti...eDuplicateRows

Neil

"BigRog" wrote in message
...
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