rajendra,

1. The Average function ignores text, logical values and empty cells,
so if you clear the row each time, you won't need a dynamic range.

2. Use a variable that contains the number of simulations...
Dim lngSims as Long
lngSims = 1001
....
Selection.AutoFill Destination:=Range("A2",Cells(lngSims,1))

Jim Cone
San Francisco, USA


"rsankh" wrote in message
...

hey !!!
please help ...
1).
I have recorded a macro which calculates average of some sample.
it uses the formula "=AVERAGE(A1:AD1)" (average of 30 data)
when I run the macro next time I have only 20 data in the sample.
I want macro to calculate it as "=AVERAGE(A1:T1)"
How to make the range to be variable?

2).
I calculated the average for the first row, Then I pasted the values
for next 2000 cells.
here are the code lines ..

ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet1!R[-1]C:R[-1]C[29])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A2001"),
Type:=xlFillDefault

next time when I run the macro I have only 1000 simulations and I want
to autofill the range as "A2:A1001".
but with the recorded macro it is autofilling till A2001.
How to put variable in this ?

Thanks in advance...
--rajendra