Hi Chiew Yen

If you need to have the formula expand downwards when data is added, then I suggest you convert your range into an Excel Table.

In an Excel Table, the formula in column F will auto copy downwards whenever any new record is added. No need to use any macro.

BTW the copy macro above can be shortened to

Sub CopyDownFormula()

Range("F5").Copy Range("F6:F30")

End Sub

Hope this helps.

Sunny

Good Idea Sunny. However, I am having a complicated coding from copy raw data into summary sheet with adding new column --> highlight the new column --> add the formula for new column and need to draw a spark-line at new column call trend. In addition, need to do the mapping by WW.

This macro coding make me headache for few days. I need to submit my project by this Saturday.  

Do you have any better idea to share with me? attached is my testing file. 

Thank you.

Rgds,

CY

For a variable length column you can add a variable like the below then use that as part of the range

Sub LastRowX()

Dim LastRow As Long

LastRow = Cells(Rows.Count, "a").End(xlUp).Row 'the "a" is the column you want to know the last row on

Range("A1:a" & LastRow).Select 'this would select the range

End Sub

The range can then be selected/copied or what ever with the 

Probably the most useful thing i have added to my Macros

Hi Purfleet,

Thank you for your suggestion. However, I still not get the formula copy. Anything wrong with my coding? I used both method for the last row and the range. But still not able to get the formula copy. Could you pls help to correct me if I am wrong? Thank you.

Sub Formula()
'Formula Macro

Range("F6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(AVERAGE(RC[11]:RC[15])),0,AVERAGE(RC[11]:RC[15]))"
Range("F6").Select

Range("F6").Copy

Dim LastRow As Long

'LastRow = Cells(Rows.Count, "F").End(xlUp).Row 'the "a" is the column you want to know the last row on

Range("F5:F5" & LastRow).Select 'this would select the range

not sure what your data looks like, but you seem to be counting the rows in column F with

LastRow = Cells(Rows.Count, "F").End(xlUp).Row 'the "a" is the column you want to know the last row on

but the only thing in F is the formula from

Range("F6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(AVERAGE(RC[11]:RC[15])),0,AVERAGE(RC[11]:RC[15]))"

You also can simplfiy the code by getting rid of the select & copy

Sub Formula()
'Formula Macro

Dim LastRow As Long

LastRow = Cells(Rows.Count, "E").End(xlUp).Row ' NUMBER ROW YOU HAVE IN THE DATA

Range("F5:F" & LastRow) = "=IF(ISERROR(AVERAGE(RC[11]:RC[15])),0,AVERAGE(RC[11]:RC[15]))"

End Sub

Explanation

Dim LastRow As Long - Sets the Variable

LastRow = Cells(Rows.Count, "E").End(xlUp).Row ' NUMBER ROW YOU HAVE IN THE DATA - Counts the rows in Column E

Range("F5:F" & LastRow) = "=IF(ISERROR(AVERAGE(RC[11]:RC[15])),0,AVERAGE(RC[11]:RC[15]))" - Creates the formula in each cell in F5 to F and the number of the lastrow

Thank you Purfleet. It really helps. Thank you.