Extend formulas using macro

Posted on 2011-10-06
Last Modified: 2012-08-13

I have tons of formulas on nearly 20 tabs (each consisting 30 col x 1000 rows). Half of the tables are not populated with user data but the formula is there in anticipation so that if a user enters something, the fields would be populated accordingly.

The problem is, my file is now 55mb. I want to reduce it and done some research. I've seen one way is to reduce redundant formulas.

Is there a way to extend formula above based on a cell in a column being populated?

Such that, write a macro to see if A1 is populated, then the macro would pull down B1, C1, D1, etc which would house seperate formulas

I understand you can get the formula into VBA (dynamic) but I want to see if above is possibile?

If then any directions is much appreciated!

I've got the following code (please see below) but I want to see if this can be done live. Especially, in accordance with the user input (say user inputs 800 rows, then the formula goes down 800 rows)

Thank you!

Question by:Shanan212
    LVL 13

    Author Comment

        Selection.AutoFill Destination:=Range("W10000:AA10017"), Type:= _

    Open in new window

    While waiting, I experimented with macros and found out that the above works.

    I do want to see if this can be done live. If then, how? Thanks!
    LVL 24

    Expert Comment

    Maybe not perfect, but have you checked whether (in Excel 20030 Tools > Options > Edit > Extend data range and formula is ticked?
    LVL 13

    Accepted Solution

    Sub FormulaFiller()  
    Dim rgCopy As Range, rgDelete As Range, rgFill As Range  
    Set rgCopy = [E2:H2]    'Formulas in these cells will be copied down  
    Set rgFill = [A2]       'First piece of raw data  
    Set rgFill = Range(rgFill, rgFill.Worksheet.Cells(Rows.Count, rgFill.Column).End(xlUp))  'All data in that column  
    Set rgFill = rgCopy.Resize(rgFill.Rows.Count)       'Fill formulas down in this range  
    Set rgDelete = rgCopy.Offset(1, 0).Resize(rgCopy.Worksheet.UsedRange.Rows.Count)  
    rgCopy.AutoFill rgFill, xlFillCopy  
    End Sub 

    Open in new window

    The above is what I was looking for from this thread!
    LVL 13

    Author Closing Comment

    Thanks experts!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now