PDA

View Full Version : Deleting Rows That contain specific content in two columns



josephir
07-28-2014, 06:05 AM
Let me preface by saying I am not highly knowledgeable in this context. I found some code online and tailored it to my needs. When I run it, it does what I want it to do (delete a row the contains one value in column K and one value in column J), but I don't believe the code is running how I want it too, and its will loop continuously. Any help would be greatly appreciated.

My code:


Sub DeleteRows()

Sheets("Sheet1").Select
Dim rFind As Range
Dim rFind2 As Range
Dim rDelete As Range
Dim strSearch As String
Dim iLookAt As Long
Dim bMatchCase As Boolean

strSearch = Range("AJ1")
strSearch2 = Range("AK1")

iLookAt = xlWhole
bMatchCase = False

Set rDelete = Nothing

Application.ScreenUpdating = False
With Sheet1.Columns("J:J")
Set rFind2 = .Find(strSearch2, LookIn:=xlValues, LookAt:=iLookAt, SearchDirection:=xlPrevious, MatchCase:=bMatchCase)
If Not rFind2 Is Nothing Then
Do
With Sheet1.Columns("K:K")
Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=iLookAt, SearchDirection:=xlPrevious, MatchCase:=bMatchCase)
If Not rFind Is Nothing Then
Do
Set rDelete = rFind
Set rFind = .FindPrevious(rFind)
If rFind.Address = rDelete.Address Then Set rFind = Nothing
rDelete.EntireRow.Delete
Loop While Not rFind Is Nothing
End If
End With
Loop While Not rFind2 Is Nothing
End If
End With
Application.ScreenUpdating = True
End Sub

patel
07-28-2014, 07:23 AM
can you attach a sample file for testing ?