PDA

View Full Version : Delete rows



oleg_v
03-14-2012, 10:03 PM
Hello,
I am having some trouble with deleting rows.
i attached a file and what wanted is:
to check if the value in the rows is adentical in all cell then to delete all the rows besides one.
there is no importance which row not to delete as long as one left from all adentical rows.

i will appreciate your help

Oleg

frank_m
03-14-2012, 11:56 PM
Hi oleg_v (http://www.vbaexpress.com/forum/member.php?u=26102)

I didn't look at your file, but the KB entry at the link below looks to me to do what you described.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=135

oleg_v
03-15-2012, 12:29 AM
Thank you very much.
but how a can applay this to 3 columns at the same time.
because the macro is refered to check only according to column A and i need A,B,C


thanks

frank_m
03-15-2012, 01:10 AM
With a disposable copy of your data, thoroughly test this

Option Explicit

Sub DeleteDupsColsABC()

Dim i As Long
Dim x As Long
Dim LastRow As Long

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Application.ScreenUpdating = False

For i = 1 To 3 'Col's 1 thru 3
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range(Cells(x, i), _
Cells(LastRow, i)), Cells(x, i).Value) > 1 Then
Cells(x, i).EntireRow.Delete
End If
Next x
Next i

Application.ScreenUpdating = True
End Sub