• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to copy the formula from the 1st cell to the end of the entire row which has data?|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / How to copy the formula from the 1st cell to the end of the entire row which has data?|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosHow to copy the formula from the 1s…
sp_PrintTopic sp_TopicIcon
How to copy the formula from the 1st cell to the end of the entire row which has data?
Avatar
Guest
Guest
Guests
1
December 20, 2019 - 1:19 am
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
December 20, 2019 - 2:24 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Guest
Guest
Guests
3
December 20, 2019 - 5:39 pm
sp_Permalink sp_Print

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

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
December 20, 2019 - 10:15 pm
sp_Permalink sp_Print

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

Avatar
Guest
Guest
Guests
5
December 23, 2019 - 12:13 am
sp_Permalink sp_Print

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

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
6
December 23, 2019 - 1:26 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Guest
Guest
Guests
7
December 26, 2019 - 12:41 am
sp_Permalink sp_Print

Thank you Purfleet. It really helps. Thank you.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ngoc Tinh
Guest(s) 11
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6215
Posts: 27248

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.