Deleting visible duplicates of visible rows only
New at VBA. I am using the code below to identify and delete rows in Sheet1 which have duplicate values in Column C of Sheet2, but I need the code to not delete rows that are already hidden from a filter.
I have searched around and tried using .SpecialCells(xlCellTypeVisible) but I don't know where to place it. Another option I thought was using EntireRow.Hidden syntax but I am not sure how to incorporate that either.
Any help is appreciated.
Sub DeleteDuplicates() Application.ScreenUpdating = False Dim Row As Long Dim FoundDup As Range Sheets("Sheet1").Select For Row = Range("C65536").End(xlUp).Row To 2 Step -1 Set FoundDup = Sheets("Sheet2").Range("C:C").Find(Cells(Row, 3), LookIn:=xlValues, lookat:=xlWhole) If Not FoundDup Is Nothing Then Cells(Row, 3).EntireRow.Delete End If Next Row Application.ScreenUpdating = True End Sub
Add an extra condition to your If statement:
If Not FoundDup Is Nothing And Not Cells(Row,3).EntireRow.Hidden Then