How to Automatically Update Formulas or Ranges When New Rows Are Added Each
Hi everyone,
I'm an investment analyst and new to using VBA. I have a spreadsheet where I add new data every month (one new row each time). I want to use VBA to:
- Find the last row with data
- Automatically extend formulas to include the new row
- Update named ranges or charts so they stay current
What is the best way to do this in VBA?
Any simple examples or advice would be really helpful.
Thank you!
Best regards,
Noura
Hello Noura and welcome to the forum,
If I may, you're not following the right approach. There are native Excel tools that allow you to do this in a simple and intuitive way, without using VBA (old, security concerns, subject to bugs…).
Based on your description, it sounds like you're looking for tables. Here's a great tutorial that gives a comprehensive overview of their features: Excel table: comprehensive tutorial with examples.
For your information, in addition to automatically extending your formulas, this will also apply to pivot tables.
Hi, I totally agree with Saboh. But if you still want to use VBA, here is some starting points :
Last row based on column A :
LR = Sheets("SheetName").Range("A" & Rows.Count).End(xlUp).RowWriting formula with VBA :
Sheets("SheetName").Range("A1").Formula = "=SUM(B1:B" & LR & ")"You can also use generic formulas like =SUM(B:B), but those ones use quite more ressources.