Extend formulas using macro


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!

LVL 13
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shanan212Author Commented:
    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!
Maybe not perfect, but have you checked whether (in Excel 20030 Tools > Options > Edit > Extend data range and formula is ticked?
Shanan212Author Commented:
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!


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shanan212Author Commented:
Thanks experts!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.