A
alexm999
How do I delete 3 rows after the rows that starts off with the wor
"ADDRESS" ?
"ADDRESS" ?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
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)
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)
A
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
about 100 times. I'd like to scan through the whole spreadsheet an
delete 3 rows after each word ADDRES
B
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)
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)
A
alexm999
Thanks!! Did the trick
J
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,
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 said: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)
T
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
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 said: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,