Specifying ranges relative to a cell
 

I've used automation with Excel before, and each time I do, it seems to be
so complicated to accomplish conceptually basic things. In this case, I want
to be able to find each cell on a worksheet that contains a particular text
string (which I know how to do) and, for each one, to delete the row
containing that cell and the next N rows as well. Is there a simple way to
do this?

--
Harlan Messinger
Remove the first dot from my e-mail address.
Veuillez ôter le premier point de mon adresse de courriel.

Specifying ranges relative to a cell
 

In this example, cRow is the number of the row containing the cel
you've found and n is the additional number of rows you want to delet
BELOW the first.

For x = cRow + n To cRow Step -1
Rows(x).Delete
Next x

Be sure to delete from the bottome to the top as seleting from the to
don is a headache.
How's that? - Piku

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

Specifying ranges relative to a cell
 

i = FindRange.Ro
Rows(i).Resize(N+1).EntireRow.Delete
'<< Where N is the number of Rows you deleted besides the Row where the value was foun

----- Harlan Messinger wrote: ----

I've used automation with Excel before, and each time I do, it seems to b
so complicated to accomplish conceptually basic things. In this case, I wan
to be able to find each cell on a worksheet that contains a particular tex
string (which I know how to do) and, for each one, to delete the ro
containing that cell and the next N rows as well. Is there a simple way t
do this

--
Harlan Messinge
Remove the first dot from my e-mail address
Veuillez ôter le premier point de mon adresse de courriel

Specifying ranges relative to a cell
 

"pikus " wrote in message
...
In this example, cRow is the number of the row containing the cell
you've found and n is the additional number of rows you want to delete
BELOW the first.

For x = cRow + n To cRow Step -1
Rows(x).Delete
Next x

Be sure to delete from the bottome to the top as seleting from the top
don is a headache.
How's that? - Pikus

Looks simple enough. I'll try it. I guess I've been attacking the problem
from the wrong angle. Well, not really--I figured that returning a set of
adjacent rows as a range, and then deleting them as a unit, would be more
efficient than deleting one row at a time. But maybe that's not feasible.

Specifying ranges relative to a cell
 

Assuming that the found cell is i n the variable rng

rng.Resize(10,1).entirerow.delete

adjust the 10 to your N

--

HTH

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

"Harlan Messinger" wrote in message
...
I've used automation with Excel before, and each time I do, it seems to be
so complicated to accomplish conceptually basic things. In this case, I
want
to be able to find each cell on a worksheet that contains a particular
text
string (which I know how to do) and, for each one, to delete the row
containing that cell and the next N rows as well. Is there a simple way to
do this?

--
Harlan Messinger
Remove the first dot from my e-mail address.
Veuillez ôter le premier point de mon adresse de courriel.

Specifying ranges relative to a cell
 

"Bob Phillips" wrote in message
...
Assuming that the found cell is i n the variable rng

rng.Resize(10,1).entirerow.delete

adjust the 10 to your N


Somehow I never came across those properties before. I'd swear Microsoft's
programming Help modules used to be more helpful before the 2000 versions of
everything. Anyway, thanks. (Why is Resize a property instead of a method?)

Specifying ranges relative to a cell
 

because it returns a range object, it doesn't do anything it itself (unlike
say delete method).

--

HTH

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

"Harlan Messinger" wrote in message
...

"Bob Phillips" wrote in message
...
Assuming that the found cell is i n the variable rng

rng.Resize(10,1).entirerow.delete

adjust the 10 to your N


Somehow I never came across those properties before. I'd swear Microsoft's
programming Help modules used to be more helpful before the 2000 versions
of
everything. Anyway, thanks. (Why is Resize a property instead of a
method?)

Specifying ranges relative to a cell
 

"Bob Phillips" wrote in message
...
because it returns a range object, it doesn't do anything it itself
(unlike
say delete method).

I know you meant well, but that actually doesn't explain it. :-) Good
programming practice breaks methods (functions, procedures, routines) into
two general categories:

1. Routines whose purpose is to modify data. These routines shouldn't return
anything, except possibly a status code.

2. Routines whose purpose is to return data. These routines shouldn't change
anything.

In other words, it's perfectly normal for functions to return information
without changing anything--the two go hand in hand.

What generally distinguishes properties from the second class of methods is
that a property generally represents an intrinsic attribute of the object it
belongs to--such as the length of a string, the radius of a circle, the
background color of a shape. It is rather transparent to the programmer that
he is dealing with a property rather than a member variable or field. For
example, it is syntactically correct to assign a value *to* a property
(though, semantically, a particular property may be designated read-only):

MyText.Length = 5

A range that contains another range as well as its entire row and nine other
rows isn't conceptually a property or an attribute of the original range.
It's the product of a computation that uses the original range as a starting
point. So, conceptually, I would have expected a method rather than a
property. (Compare the idea that for a Person object, Employer or Mother or
Children are reasonably thought of as attributes or properties of the
person, while "all the people who live on the same block as the Person"
really isn't a basic attribute of the Person but, rather, something that can
be computed *for* the Person.)

Specifying ranges relative to a cell
 

Well I clearly don't know good programming practice so I can't help you
anymore.

--

HTH

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

"Harlan Messinger" wrote in message
...

"Bob Phillips" wrote in message
...
because it returns a range object, it doesn't do anything it itself
(unlike
say delete method).

I know you meant well, but that actually doesn't explain it. :-) Good
programming practice breaks methods (functions, procedures, routines) into
two general categories:

1. Routines whose purpose is to modify data. These routines shouldn't
return
anything, except possibly a status code.

2. Routines whose purpose is to return data. These routines shouldn't
change
anything.

In other words, it's perfectly normal for functions to return information
without changing anything--the two go hand in hand.

What generally distinguishes properties from the second class of methods
is
that a property generally represents an intrinsic attribute of the object
it
belongs to--such as the length of a string, the radius of a circle, the
background color of a shape. It is rather transparent to the programmer
that
he is dealing with a property rather than a member variable or field. For
example, it is syntactically correct to assign a value *to* a property
(though, semantically, a particular property may be designated read-only):

MyText.Length = 5

A range that contains another range as well as its entire row and nine
other
rows isn't conceptually a property or an attribute of the original range.
It's the product of a computation that uses the original range as a
starting
point. So, conceptually, I would have expected a method rather than a
property. (Compare the idea that for a Person object, Employer or Mother
or
Children are reasonably thought of as attributes or properties of the
person, while "all the people who live on the same block as the Person"
really isn't a basic attribute of the Person but, rather, something that
can
be computed *for* the Person.)