X
Xman019
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
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