Log In
Register
Home
Hi,
Anyone can help me to understand, why cannot put the range as F: F to copy the formula for the entire column F from cell F5 instead of need to specify the range F6:F30? What if in future, the row of data increase from row#30 to 50?
Sub CopyDownFormula()
'
' CopyDownFormula Macro
'
Range("F5").Select
Selection.Copy
Range("F6:F30").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=-15
End Sub
Thank you
CY
VIP
Trusted Members
June 25, 2016
Offline
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
Trusted Members
December 20, 2019
Offline
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
Trusted Members
December 20, 2019
Offline
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
