"like" string
 

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

"like" string
 

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

MDC wrote:

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

"like" string
 

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



"MDC" wrote in message
...
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

"like" string
 

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.

--
Regards,
Tom Ogilvy

"JayL." wrote in message
news:c9lob.52118$ao4.141844@attbi_s51...
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



"MDC" wrote in message
...
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

"like" string
 

Good Point! Thanks Tom

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"JayL." wrote in message
news:c9lob.52118$ao4.141844@attbi_s51...
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



"MDC" wrote in message
...
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