Deleting Rows That contain specific content in two columns
|
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
|
