How to delete individual rows according to criteria?
 

I want to delete specific rows if one field in the row contains a particular
alphnumeric string.

How to delete individual rows according to criteria?
 

Sub Delete()
Dim myRange As Range
Dim lRow As Long
Dim lCol As Long
Dim aWS As Worksheet

Set aWS = ActiveSheet
lRow = aWS.Cells(aWS.Rows.Count, "A").End(xlUp).Row
Set myRange = Nothing
For i = 1 To lRow
lCol = aWS.Cells(lRow, aWS.Columns.Count).End(xlToLeft).Column
For j = 1 To lCol
If aWS.Cells(i, j).Value = "Your value" Then
If myRange Is Nothing Then
Set myRange = aWS.Cells(i, j)
Else
Set myRange = Union(myRange, aWS.Cells(i, j))
End If
Exit For
Next j
Next i

If Not myRange Is Nothing Then
myRange.EntireRow.Delete
End If

End Sub


Modify as needed.
--
HTH,
Barb Reinhardt



"johnabdl" wrote:

I want to delete specific rows if one field in the row contains a particular
alphnumeric string.

How to delete individual rows according to criteria?
 

Oops, I forgot an end if

Sub Delete()
Dim myRange As Range
Dim lRow As Long
Dim lCol As Long
Dim aWS As Worksheet

Set aWS = ActiveSheet
lRow = aWS.Cells(aWS.Rows.Count, "A").End(xlUp).Row
Set myRange = Nothing
For i = 1 To lRow
lCol = aWS.Cells(i, aWS.Columns.Count).End(xlToLeft).Column
For j = 1 To lCol
If lcase(aWS.Cells(i, j).text = "your value" Then
If myRange Is Nothing Then
Set myRange = aWS.Cells(i, j)
Else
Set myRange = Union(myRange, aWS.Cells(i, j))
End If
Exit For
End If
Next j
Next i

If Not myRange Is Nothing Then
myRange.EntireRow.Delete
End If

End Sub

--
HTH,
Barb Reinhardt



"johnabdl" wrote:

I want to delete specific rows if one field in the row contains a particular
alphnumeric string.