VBA macro to delete rows that contain text
 

I am a novice at using VBA code. In the past, I have used a piece of code
that deletes a row if the row contains a particular string of text in one
column.

With a new worksheet I am developing, I need to use similar code, but I need
to apply it more than once, to delete multiple rows containing several
different pieces of text.

I do not understand why, but the code works for the first criteria (i.e.
successfully deletes the row containing the first text strinig), but it will
not work on subsequent occasions.

Here is the code I am attempting to use:

Rem delete all rows on the active worksheet that have "Printed" in column AC.
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A:AJ"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Printed:" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete


Rem delete all rows on the active worksheet that have "Employee Transactions
& Totals" in column B.
For Each cell In rng
If (cell.Value) = "Employee Transactions & Totals" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete

As you can see, I want to delete rows that contains "Printed" in column AC,
and then rows that contain "Employee Transactions & Totals" in column B.
Actually, there will be several more sets of rows/text strings that I will
want to delete.

Just to give you the context, I am attempting to use a macro to re-format a
Report that is delivered from one of our applications via email in Excel
format. The report is pre-formatted and has a whole bunch of lines of
header information that I want to remove.

VBA macro to delete rows that contain text
 

Try
http://www.rondebruin.nl/delete.htm

You can try this in the code example

'Or use this two examples if you want to check more values.
ElseIf .Cells(Lrow, "A").Value = "jelle" Or _
.Cells(Lrow, "A").Value = "ron" Or _
.Cells(Lrow, "A").Value = "dave" Then .Rows(Lrow).Delete


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rod from Corrections" wrote in message
...
I am a novice at using VBA code. In the past, I have used a piece of code
that deletes a row if the row contains a particular string of text in one
column.

With a new worksheet I am developing, I need to use similar code, but I need
to apply it more than once, to delete multiple rows containing several
different pieces of text.

I do not understand why, but the code works for the first criteria (i.e.
successfully deletes the row containing the first text strinig), but it will
not work on subsequent occasions.

Here is the code I am attempting to use:

Rem delete all rows on the active worksheet that have "Printed" in column AC.
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A:AJ"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Printed:" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete


Rem delete all rows on the active worksheet that have "Employee Transactions
& Totals" in column B.
For Each cell In rng
If (cell.Value) = "Employee Transactions & Totals" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete

As you can see, I want to delete rows that contains "Printed" in column AC,
and then rows that contain "Employee Transactions & Totals" in column B.
Actually, there will be several more sets of rows/text strings that I will
want to delete.

Just to give you the context, I am attempting to use a macro to re-format a
Report that is delivered from one of our applications via email in Excel
format. The report is pre-formatted and has a whole bunch of lines of
header information that I want to remove.

VBA macro to delete rows that contain text
 

Thanks so much, Ron! It worked like a charm!

"Ron de Bruin" wrote:

Try
http://www.rondebruin.nl/delete.htm

You can try this in the code example

'Or use this two examples if you want to check more values.
ElseIf .Cells(Lrow, "A").Value = "jelle" Or _
.Cells(Lrow, "A").Value = "ron" Or _
.Cells(Lrow, "A").Value = "dave" Then .Rows(Lrow).Delete


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rod from Corrections" wrote in message
...
I am a novice at using VBA code. In the past, I have used a piece of code
that deletes a row if the row contains a particular string of text in one
column.

With a new worksheet I am developing, I need to use similar code, but I need
to apply it more than once, to delete multiple rows containing several
different pieces of text.

I do not understand why, but the code works for the first criteria (i.e.
successfully deletes the row containing the first text strinig), but it will
not work on subsequent occasions.

Here is the code I am attempting to use:

Rem delete all rows on the active worksheet that have "Printed" in column AC.
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A:AJ"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Printed:" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete


Rem delete all rows on the active worksheet that have "Employee Transactions
& Totals" in column B.
For Each cell In rng
If (cell.Value) = "Employee Transactions & Totals" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete

As you can see, I want to delete rows that contains "Printed" in column AC,
and then rows that contain "Employee Transactions & Totals" in column B.
Actually, there will be several more sets of rows/text strings that I will
want to delete.

Just to give you the context, I am attempting to use a macro to re-format a
Report that is delivered from one of our applications via email in Excel
format. The report is pre-formatted and has a whole bunch of lines of
header information that I want to remove.