macro to conditionally delete 3-row blocks in excel

[Guest]

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

 

[Susan]

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

 

[Guest]

Hi Susan, I appreciate the quick response...

What you're describing is basically what my macro does (it works in the
spreadsheet i originally used it for, anyway)... instead of putting an 'x' in
another column it just saves the cell location it found 'info' in to a
string, then when it finds another one, it unions that location to the same
string. then when it's ready to delete, it selects all of the rows in the
string and deletes them all at once to avoid the row number confusion. The
problem is I can't figure out the language I need to use in order to have it
add not just the location of the cell with the 'info' in it, but also the
location of the cells above and below that cell.
Anyone have any ideas on how to do that?

Anyone... Anyone... Anyone...
Bueller... Bueller... Bueller

--
Marty

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

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

 

[Norman Jones]

Hi Marty,

Try something like:

'============>>
Public Sub DeleteRange()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range
Dim delRng As Range
Dim iLastRow As Long
Dim CalcMode As Long
Const sstr As String = "Info"

Set WB = Workbooks("MyBook.xls") '<<=== CHANGE
Set SH = WB.Sheets("Sheet1") b '<<=== CHANGE

With SH
iLastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set Rng = .Range("D1" & iLastRow)
End With

On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rCell In Rng.Cells
With rCell
.Select
If StrComp(.Value, sstr, vbTextCompare) = 0 Then
If delRng Is Nothing Then
Set delRng = rCell.Offset(-1).Resize(3)
Else
Set delRng = Union(rCell.Offset(-1).Resize(3), delRng)
End If
End If
End With
Next rCell

If Not delRng Is Nothing Then
delRng.delate
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<============

 

[Norman Jones]

Hi Marty,

Set SH = WB.Sheets("Sheet1") b '<<=== CHANGE

should read:

Set SH = WB.Sheets("Sheet1") '<<=== CHANGE

 

[Norman Jones]

Hi Marty,

Apologies - I forgot to remove a test line:

.Select

In 'real' code it is rarely desirable or efficient to make
selections and I should have deleted this line before
posting the code.

 

[tissot.emmanuel]

Hi,

Does the following sub work as expected?

Sub DeleteHeaderRows()
Dim rng As Range, rngToSearch As Range
Application.ScreenUpdating = False
With ActiveSheet
Set rngToSearch = .Range(.Range("D1"), .Range("D65536").End(xlUp))
On Error Resume Next
Do
Set rng = rngToSearch.Find(what:="INFO", LookIn:=xlValues,
lookat:=xlWhole, MatchCase:=True)
Union(rng, rng.Offset(-1, 0), rng.Offset(1, 0)).Value =
CVErr(xlErrNA)
Loop While Not rng Is Nothing
rngToSearch.SpecialCells(xlCellTypeConstants,
xlErrors).EntireRow.Delete
End With
End Sub

Best regards from France,

Manu/

Hi Susan, I appreciate the quick response...

What you're describing is basically what my macro does (it works in the
spreadsheet i originally used it for, anyway)... instead of putting an 'x'
in
another column it just saves the cell location it found 'info' in to a
string, then when it finds another one, it unions that location to the
same
string. then when it's ready to delete, it selects all of the rows in the
string and deletes them all at once to avoid the row number confusion.
The
problem is I can't figure out the language I need to use in order to have
it
add not just the location of the cell with the 'info' in it, but also the
location of the cells above and below that cell.
Anyone have any ideas on how to do that?

Anyone... Anyone... Anyone...
Bueller... Bueller... Bueller

--
Marty

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

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

 

[Norman Jones]

Hi Marty,

And, as your intention is to delete entire rows, change:

delRng.delate

to

delRng.EntireRow.Delete

 

[Guest]

Thanks to all of you for your help on this... I was able to get it to work
using tissot.emmanuel's code! As always, this site is a lifesaver.