tricky stuff, xman............
from what i understand, you have to do this sort of thing backwards,
from the bottom up. because..........

ok, your macro has gotten to row 15, where it contains "INFO." it
deletes row 15. now row 15 used to be row 16. and if you delete row
14, well, i'm not sure now which row is which............... &
neither does your macro.
see what a mess it makes????
:)

my suggestion (& i'm no guru, & somebody will almost definitely have a
better [or more concise] idea than me) would be....

start at the bottom. i'll use column A. macro checks a15 - no
"info". macro moves to a14 - "info". macro offsets to row 15 in a
different cell & puts some sort of an indicator there, that this needs
to be deleted (later), like an "x". macro now deletes a14 & a13.
rows 14 & 13 disappear, still leaving you on row 15, which of course
is not the ORIGINAL row 15, but your macro counting has not messed
up. macro now proceeds to a14 - no "info".
etc.
then, when you're all done, search through the column where you have
the 'x's & delete any rows that have an x.

convoluted (probably waaaaaaaay too complicated - i have a horrible
habit of doing that), but i think it would work. worked in limited
manual testing, for me.

:)
susan



On Jun 1, 3:15 pm, Xman019 (donotspam) wrote:
I'm beating my head against a wall here, so I guess it's time to break down
and ask for help. I'm trying to write a macro that looks in each row in
column D on a spreadsheet, and if that cell contains "INFO", I want it to
delete that row as well as the row before it and after it. I have a macro
written that should delete that row, but i'm giving myself a headache trying
to get it to include the rows before and after it. Any ideas? Here's the
code I've started with...

sub DeleteHeaderRows

Dim rng As Range
Dim rngToDelete As Range
Dim rngToSearch As Range

With ActiveSheet
Set rngToSearch = .Range(.Range("D1"), .Cells(Rows.Count, "D").End(xlUp))
.DisplayPageBreaks = False
For Each rng In rngToSearch
If rng.Value = "INFO" Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End With
End Sub

--
Marty