How to delete a row If cell in range contains specific text
Category: [Excel] Demo Available
How to delete a row If cell in range contains specific text
Requirement:
In range A6:A502 the user needs to delete row(s) if that range contains "Thumbs.db"
The Code the user has below does the job, but takes a long time to go through each cell in range and triggers other code due to Selection Change, which the user does not want happening, so needs a better solution.
Code:
Sub RemoveLines()
Dim Counter As Long
' Start at
Range("A6").Select
For Counter = 1 To 505
If ActiveCell.Value Like "Thumbs.db" Then
ActiveCell.EntireRow.Delete ' Delete Row
Else
ActiveCell.Offset(1, 0).Select ' Move down a row
End If
Next Counter
End Sub
Solution:
Code:
Sub deleteRows()
With Range("A6:A502")
.Replace "Thumbs.db", "#N/A", xlWhole
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to delete rows containing certain keywords in cells |
| How to delete rows with no value |
| How to loop each row if there is data after green colour cell then delete |
| How to use VBA to delete rows based on multiple conditions |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.