"like" string

[MDC]

I need to delete a row that has certain values in a cell
in Column A. For example if the word "Average" appears in
cell a, I need to delete the whole row. I can't figure
out how to write the code to find partial string match.

Thanks

 

[Dave Peterson]

Maybe you could apply a filter to column A and look for Contains: Average.

Then delete those visible cells. (record a macro when you do it once for the
code).

or

Option Explicit
Sub testme01()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim delRng As Range

With Worksheets("sheet1")
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If InStr(1, .Cells(iRow, 1).Value, "average", _
vbTextCompare) > 0 Then
If delRng Is Nothing Then
Set delRng = .Cells(iRow, 1)
Else
Set delRng = Union(delRng, .Cells(iRow, 1))
End If
End If
Next iRow

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireRow.Delete
End If
End With
End Sub

 

[JayL.]

This macro will delete a row if the 3 letters 'Ave' are the 1st 3 letters
in the cell
Easily adapted.

Sub delete_if_Ave()
For Each c In Selection
If Left(c, 3) = "Ave" Then c.EntireRow.Delete
Next
End Sub

 

[Tom Ogilvy]

That will tend to skip some rows if two or more consecutive rows contain the
condition.

Sub delete_if_Ave()
Dim lrow as long, i as long, c as range
lrow = selection(selection.rows.count).Row
For i = lrow to selection.row step -1
set c = cells(i,activecell.column)
If Left(c, 3) = "Ave" Then c.EntireRow.Delete
Next
End Sub

so looping from the highest row to the lowest row avoids this problem.

 

[JayL.]

Good Point! Thanks Tom

That will tend to skip some rows if two or more consecutive rows contain the
condition.

Sub delete_if_Ave()
Dim lrow as long, i as long, c as range
lrow = selection(selection.rows.count).Row
For i = lrow to selection.row step -1
set c = cells(i,activecell.column)
If Left(c, 3) = "Ave" Then c.EntireRow.Delete
Next
End Sub

so looping from the highest row to the lowest row avoids this problem.