PDA

View Full Version : Compare and delele cells with equal value



Felipe Dasi
01-12-2009, 05:17 AM
How I can delete all the cells with equal values?

Example:

A B C D E F G
1 1 1 1 5 1 1

The correct final result is: 5

Tks.

Bob Phillips
01-12-2009, 06:10 AM
Public Sub ProcessData()
Dim i As Long
Dim LastCol As Long
Dim cell As Range
Dim sh As Worksheet

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

.Rows(2).Insert
.Rows(1).Copy .Range("A2")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastCol To 1 Step -1

If Application.CountIf(.Rows(1), .Cells(2, i).Value) > 1 Then

.Cells(2, i).Delete shift:=xlShiftToLeft
End If
Next i
.Rows(1).Delete

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

Felipe Dasi
01-12-2009, 06:20 AM
tanks xld, for your answer.