I am wondering if there is a macro that can be written that will delete a row
and also any reference to any cells in that row from froumulas on the sheet.
For example: A1=B1+B2+B3 i then delete row 2 and get A1=B1+#REF+B3, i would
like to get A1=B1+B3. Is something like this possible? Maybe when running
the delete row macro it would do a find and replace? Any thoughts? Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200705/1

look at DirectPrecedents property of the cell to be deleted before you delete
it. Loop through them and change the formula.

--
Regards,
Tom Ogilvy


"brownti via OfficeKB.com" wrote:

> I am wondering if there is a macro that can be written that will delete a row
> and also any reference to any cells in that row from froumulas on the sheet.
> For example: A1=B1+B2+B3 i then delete row 2 and get A1=B1+#REF+B3, i would
> like to get A1=B1+B3. Is something like this possible? Maybe when running
> the delete row macro it would do a find and replace? Any thoughts? Thanks
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200705/1
>
>

I am trying to streamline a process. I have a sheet with with rows that have
cells that contain formulas. Some of those rows get deleted while using the
sheet however there are a group of cells that refrence some of those cells.
i would like to be able to right click the row and instead of Delete Row it
would be Delete row and References and that would take care of the problem
for me. It would be a lot quicker than cycling through formulas and changing
them. Ideas?


Tom Ogilvy wrote:
>look at DirectPrecedents property of the cell to be deleted before you delete
>it. Loop through them and change the formula.
>
>> I am wondering if there is a macro that can be written that will delete a row
>> and also any reference to any cells in that row from froumulas on the sheet.
>> For example: A1=B1+B2+B3 i then delete row 2 and get A1=B1+#REF+B3, i would
>> like to get A1=B1+B3. Is something like this possible? Maybe when running
>> the delete row macro it would do a find and replace? Any thoughts? Thanks

--
Message posted via http://www.officekb.com

It certainly would.

(E-Mail Removed)

--
Regards,
Tom Ogilvy


"brownti via OfficeKB.com" wrote:

> I am trying to streamline a process. I have a sheet with with rows that have
> cells that contain formulas. Some of those rows get deleted while using the
> sheet however there are a group of cells that refrence some of those cells.
> i would like to be able to right click the row and instead of Delete Row it
> would be Delete row and References and that would take care of the problem
> for me. It would be a lot quicker than cycling through formulas and changing
> them. Ideas?
>
>
> Tom Ogilvy wrote:
> >look at DirectPrecedents property of the cell to be deleted before you delete
> >it. Loop through them and change the formula.
> >
> >> I am wondering if there is a macro that can be written that will delete a row
> >> and also any reference to any cells in that row from froumulas on the sheet.
> >> For example: A1=B1+B2+B3 i then delete row 2 and get A1=B1+#REF+B3, i would
> >> like to get A1=B1+B3. Is something like this possible? Maybe when running
> >> the delete row macro it would do a find and replace? Any thoughts? Thanks
>
> --
> Message posted via http://www.officekb.com
>
>