How to Create a Static Range when Deleting Rows

Posted by David Holstein on February 20, 2000 4:00 PM

Hi,

I have a macro that allows me to delete rows by ActiveCell.EntireRow feature. It then recreates my formulas at Row 10 and uses Autofill to copy the formulas to Row 1009 (gives me 1000 rows).

The problem I am having is when I delete the rows, the formulas are never copied down to Row 1009. I have tried Naming the Range F11:Q1009 and using the name as a reference, but when I look at the range properties of the NAME after deleting rows, it is always less then 1009.

Thanks in Advance
Regards
David

Posted by Celia on February 21, 2000 12:49 AM

David
There are probably better ways, but here is one suggestion.
When a row or rows are deleted, the formulas could be re-entered in the "missing" rows at the end by adding some copy and paste code to your macro after the delete code.
For example, if there is a formula in column C :-

Range("C10").Copy
ActiveSheet.Paste Destination:=Range(Range("C1009"),Range("C1009").End(xlUp).Offset(1, 0))

Celia

Posted by Celia on February 21, 2000 12:53 AM

David
There are probably better ways, but here is one suggestion.
When a row or rows are deleted, the formulas could be re-entered in the "missing" rows at the end by adding some copy and paste code to your macro.
For example, if there is a formula in column C :-

Range("C1").Copy
ActiveSheet.Paste Destination:=Range(Range("C1009"),Range("C1009").End(xlUp).Offset(1, 0))

Celia

Posted by Celia on February 21, 2000 7:20 PM

David

When assigning a name to your range, put the following in the Refers To box :

=INDIRECT("F11:Q1009")

This should fix the cells even if you delete rows.

Celia