VBA to delete specific rows in a list
I have a list of some 20 columns and thousands of rows. What I want to do is develop code that will delete an entire row if the content in column K = AJ1, and the content in column J = AK1.
This is what I have, but I cannot figure out how to make the search criteria for both columns:
Sub TestDeleteRows()
Sheets("Sheet1").Select
Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
Dim iLookAt As Long
Dim bMatchCase As Boolean
strSearch = Range("AJ1")
iLookAt = xlWhole
bMatchCase = False
Set rDelete = Nothing
Application.ScreenUpdating = False
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
Application.ScreenUpdating = True
End Sub
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago