Specifying ranges relative to a cell

H

Harlan Messinger

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?
 
P

pikus

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
 
C

chris

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
 
H

Harlan Messinger

pikus > said:
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
Click to expand...

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.
 
B

Bob Phillips

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)
 
H

Harlan Messinger

Bob Phillips said:
Assuming that the found cell is i n the variable rng

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

adjust the 10 to your N
Click to expand...

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?)
 
B

Bob Phillips

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)
 
H

Harlan Messinger

Bob Phillips said:
because it returns a range object, it doesn't do anything it itself (unlike
say delete method).
Click to expand...

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.)
 
B

Bob Phillips

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)
 
Top