Deleting rows
 

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

--
Message posted from http://www.ExcelForum.com

Deleting rows
 

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 " wrote in message
...
How do I delete 3 rows after the rows that starts off with the word
"ADDRESS" ??


---
Message posted from http://www.ExcelForum.com/

Deleting rows
 

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

--
Message posted from http://www.ExcelForum.com

Deleting rows
 

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 " wrote in message
...
How do I loop it, I have the word ADDRESS in my spreadsheet showing up
about 100 times. I'd like to scan through the whole spreadsheet and
delete 3 rows after each word ADDRESS


---
Message posted from http://www.ExcelForum.com/

Deleting rows
 

Thanks!! Did the trick

--
Message posted from http://www.ExcelForum.com

Deleting rows
 

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,


"Bob Phillips" wrote in message
...
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 " wrote in message
...
How do I loop it, I have the word ADDRESS in my spreadsheet showing up
about 100 times. I'd like to scan through the whole spreadsheet and
delete 3 rows after each word ADDRESS


---
Message posted from http://www.ExcelForum.com/

Deleting rows
 

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

"JMay" wrote in message
news:uJG%b.13272$TT5.3326@lakeread06...
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,


"Bob Phillips" wrote in message
...
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 " wrote in message
...
How do I loop it, I have the word ADDRESS in my spreadsheet showing up
about 100 times. I'd like to scan through the whole spreadsheet and
delete 3 rows after each word ADDRESS


---
Message posted from http://www.ExcelForum.com/