Delete rows where

[Annette]

Col B contain "STAMPS" ...

I want to be able to delete any row and the one directly under it that has
the word STAMPS in Col B.

How can I programmically do this. Thanks for your help.

Annette

 

[Tom Ogilvy]

Sub DeleteRows()
Dim lastrow as long, i as long
lastrow = cells(rows.count,2).End(xlup)
for i = lastrow to 1 step -1
if instr(1,cells(i,2),"stamps",vbTextCompare) then
cells(i,1).Resize(2).EntireRow.delete
end if
Next
End Sub

 

[Frank Kabel]

Hi Annette
try the following macro

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If Trim(Cells(RowNdx, "B").Value) ="STAMPS" then
Rows(RowNdx).Delete
Rows(RowNdx+1).Delete
End If
Next RowNdx
End Sub

 

[Don Guillett]

try this
Sub nostamps()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 1 Step -1
If Cells(i, "b") = "stamps" Then Cells(i, "b").Resize(2, 1).EntireRow.Delete
Next
End Sub

 

[Tom Ogilvy]

This deletes the row with Stamps and the second row beneath it (rather than
the row beneath it)

You need to delete the lower row first
Rows(RowNdx+1).Delete
Rows(RowNdx).Delete

or do the command twice
Rows(RowNdx).Delete
Rows(RowNdx).Delete

or use the Resize method.

 

[Frank Kabel]

Hi Tom
thanks for the correction. I always mess the order in these deletions

 

[Kevin Beckham]

However, have to be careful of what happens when
STAMPS occurs in two successive cells. Do you want to
delete 3 rows or 4 rows?

If you only want to delete 3 rows then set a last row
two cells further down, and start from the top, checking
against the set last row.

Kevin Beckham

 

[Annette]

Works Great! ...


Thanks!

Sub DeleteRows()
Dim lastrow as long, i as long
lastrow = cells(rows.count,2).End(xlup)
for i = lastrow to 1 step -1
if instr(1,cells(i,2),"stamps",vbTextCompare) then
cells(i,1).Resize(2).EntireRow.delete
end if
Next
End Sub

 

[Tom Ogilvy]

A condition was not set for successive entries of STAMPS in column B. I
considered the possibility, but figured it would be mention either now (or
in a subsequent post if it was/is an issue).

The OP would have to state what actions need to be taken in this instance.

Adding rows to the lastrow wouldn't seem to serve any useful purpose as
decisions are made on the cells containing STAMPs and rows beyond the last
are empty anyway. The existing code could be easily modified to check for
adjacent cells containing STAMPS.