I have a table that has many redundant rows (same computer name in more than
1 row). Is there a way to automatically delete these rows so that they will
be only one instance of a given computer name in the list? Thanks

James

I found this on the web quite a few years ago and it works for me (probably
from Chip Pearson's site but I cannot say for sure).

-ryanb.-

Paste this in a macro, then select the first cell in the column of computer
names and run it.

Sub DeleteDuplicates()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell. Select the first row of
' the column with duplicate numbers and then run the macro.

Dim Col As Integer
Dim R As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

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).EntireRow.Delete
N = N + 1
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

HTH

Hi. This is indeed on Chip Pearson's web-site. There is therefore a
presumption that it works okay.

However, I am a bit puzzled, because it seems to me as though it looks for
duplicates in the first column of the used range on the spreadsheet, rather
than the column in which the active cell is located. The value Col is
calculated but not used (and in any case might not be what is wanted if
usedrange does not start in column A).

Can somebody please explain?

Geoff






"ryanb." <rblazeiNO!@SPAMidimn.com> wrote in message
news:(E-Mail Removed)...
> I found this on the web quite a few years ago and it works for me
(probably
> from Chip Pearson's site but I cannot say for sure).
>
> -ryanb.-
>
> Paste this in a macro, then select the first cell in the column of
computer
> names and run it.
>
> Sub DeleteDuplicates()
> '
> ' This macro deletes duplicate rows in the selection. Duplicates are
> ' counted in the COLUMN of the active cell. Select the first row of
> ' the column with duplicate numbers and then run the macro.
>
> Dim Col As Integer
> Dim R As Long
> Dim C As Range
> Dim N As Long
> Dim V As Variant
> Dim Rng As Range
>
> On Error GoTo EndMacro
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
>
> Col = ActiveCell.Column
>
> If Selection.Rows.Count > 1 Then
> Set Rng = Selection
> Else
> Set Rng = ActiveSheet.UsedRange.Rows
> End If
>
> 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).EntireRow.Delete
> N = N + 1
> End If
> Next R
>
> EndMacro:
>
> Application.ScreenUpdating = True
> Application.Calculation = xlCalculationAutomatic
>
> End Sub
>
> HTH
>
>
>