In this video we show how to adjust the amortisation table based on the mortgage term. To do this, we record a macro which first deletes the excess rows and then extends the table based on the term selected. Here, we use the address function to help VBA identify until which cell to toggle the table down to. To make the mechanism look more professional, we add a few lines of VBA code that prevent the screen from flashing when the code is executed. We also use the intersect VBA method to ensure the code runs as soon as the user changes the term, thereby reducing the need for a button to run the code.
See Part 1 of the mortgage repayment calculator series:
https://youtu.be/v6n4wWXy5Us
Subscribe for weekly Excel tutorials, techniques and examples:
https://www.youtube.com/channel/UCgdRm6hepAn6Y0FqMZPLiAw?sub_confirmation1
Please share this video with others that may find it useful:
https://youtu.be/fUZU5VWNMDk
Please comment below if you have any feedback or any suggestions for videos you would like us to upload next!
The Excel Hub website: https://theexcelhub.com/
Instagram: https://www.instagram.com/theexcelhub/
ABOUT US:
The Excel Hub seeks to enhance your Excel and VBA knowledge by providing value-adding tutorials, techniques and examples in order to help you take your spreadsheet and data analysis skills to the next level.
For any enquiries, please contact:
[email protected]Overview: (0:00)
Record Macro: (0:55)
Calculate Final Cell Address: (2:05)
Improve VBA Code: (4:40)