Deleting rows

[alexm999]

How do I delete 3 rows after the rows that starts off with the wor
"ADDRESS" ?

 

[Bob Phillips]

Hi Alex,

Try this

Dim oFound As Range

Set oFound = Cells.Find("ADDRESS")
If Not oFound Is Nothing Then
oFound.Offset(1, 0).Resize(3, 1).EntireRow.Delete
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[alexm999]

How do I loop it, I have the word ADDRESS in my spreadsheet showing u
about 100 times. I'd like to scan through the whole spreadsheet an
delete 3 rows after each word ADDRES

 

[Bob Phillips]

Why didn't you say?

Dim oFound As Range
Dim firstaddress

Set oFound = Cells.Find("ADDRESS")
If Not oFound Is Nothing Then
firstaddress = oFound.Address
Do
oFound.Offset(1, 0).Resize(3, 1).EntireRow.Delete
Set oFound = Cells.FindNext(oFound)
Loop While Not oFound Is Nothing And firstaddress <> oFound.Address
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[alexm999]

Thanks!! Did the trick

 

[JMay]

Bob:
Based on your macro I set up a scenario and gave it a try. It work
perfectly, only somehow [at the end] it goes back to the beginning of the
range and deletes two additional rows, erroneously. Can you comment/alter
code to eliminate?
TIA,

Why didn't you say?

Dim oFound As Range
Dim firstaddress

Set oFound = Cells.Find("ADDRESS")
If Not oFound Is Nothing Then
firstaddress = oFound.Address
Do
oFound.Offset(1, 0).Resize(3, 1).EntireRow.Delete
Set oFound = Cells.FindNext(oFound)
Loop While Not oFound Is Nothing And firstaddress <> oFound.Address
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Post your code.

If you are deleting rows, it should not find any more values. If you are
trying to start from a certain row and only process forward (down), then you
need to put in a check for the location of the found cell and possibly alter
the search parameters prior.

Explain how it can delete all the instances of the target, yet then delete
two more rows?

--
Regards,
Tom Ogilvy

Bob:
Based on your macro I set up a scenario and gave it a try. It work
perfectly, only somehow [at the end] it goes back to the beginning of the
range and deletes two additional rows, erroneously. Can you comment/alter
code to eliminate?
TIA,