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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.