Omit/bypass cells in column equations

S

Sherry

Hello! I have a worksheet macro as follows:

Sub InsertNewRow()

ActiveSheet.Unprotect
Rows("10:10").Select
Selection.Insert Shift:=xlDown
Range("10:10").Select
Selection.Locked = True
Range("11:11").Select
Selection.Locked = False
ActiveSheet.Protect

End Sub

The of cells from and inclusive $D$11 to $D$15 are named 'Recent'

The of cell s from and inclusive $D$11 to $D$65536 are named 'EvalTotal'

I need to change this to make cells D6 display the 'Recent' Average

and D7 to display the 'EvalTotal' Average

My problems are as follows:

1) When I insert a new row using the macro shown above cell numbers change
for 'Recent' to $D$12 to $D$16 and 'EvalTotal' to $D$12 to $D$65536

2) If the cell range for 'Recent' and 'EvalTotal' change-then the results
for 'Recent Average' and 'EvalTotal Average' will not reflect an accurate
conclusion

I have tried to explain this clearly but it is difficult to do so.

Please ask for further clarification if required to lead to any helpful
suggestions

Cheers Sherry
 
D

Duke Carey

If you change the definition of the Recent range to

=OFFSET(Sheet1!$D$6,5,0,5,1)

and change EvalTotal to

=OFFSET(Sheet1!$D$6,5,0,65526,1)

they will be impervious to the changes from InsertNewRow

Alternatively, you could redefine you ranges in the macro, after the row
insertion
 
S

Sherry

OKay Hmmm!

Thanks Duke!

I think lol!

I have no idea what that means but I am trialing it now!!!

and attempting to grasp what actions it is actually perfoprming in it
execution

and.......perhaps even more importantly, how and why it bloody works!

So to rate your post would be premeture although I am sure it WILL prove to
be a valuable insite it this EXCEL programming corridoor!

Gee I will be glan when i see the light at the end of THIS corridoor!

Guess I should have included that I am absolute rank ameture and to compound
matters I am pathetic at maths (Seems spelling fits that catagory too lol)
Soooory!

I'll get back to u but thank u so far!............

Cheers Sherry
 
Top