ludoviclalo
New Member
- Joined
- Sep 26, 2022
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
I use Excel to track my personal finances. My workbook has multiple columns with each representing checking, savings, CDs, brokerages, treasuries, credit card charges, etc. Each row represents a transaction, i.e., utility payment (-), interest (+), transfer between accounts. On the first of each month, I total all of the amounts in the columns to determine my capital (aka money) so that I can compare it against prior months. The problem that I have is that some accounts are relatively static, i.e., CDs only receive 1 interest payment a month; whereas, my checking accounts have numerous transactions every month. Thus, one column might have values on 52 different rows representing a month and another column might have 1 value with a lot of blank rows for the same month. This makes it difficult to do the monthly tallying, since I need to find the last value in each column for the month before I can sum all of the columns. I have been using AutoSum and manually scrolling from the last value in each column to a row representing the end of the month to insert the last value above the row representing the end of the month, but this is rather laborious.
Is there a way, formula or VBA, to have Excel find the last row with a value in each column and insert that value in a row beneath it that may be 1 or 60 rows below it for multiple columns so that I can Autosum the row with all of the monthly totals on on row?
Referencing the uploaded image, I would like to be able to copy/paste a formula or use a macro that will pull down the last value in each column (C-U) to row 3548 without having to manually use Autosum by scrolling from D3532 to D3548, E3546 to E3548, and so on. Note: the $ amounts on the image are false, I ain't rich!
I tried an Internet search, but most searches dealt with summing rows (left to right) and not summing columns. I tried rigging some of the example that I came across to handle columns, but encountered circular reference errors since I am trying to keep the last value in each column in the same column.
Thanks.
Is there a way, formula or VBA, to have Excel find the last row with a value in each column and insert that value in a row beneath it that may be 1 or 60 rows below it for multiple columns so that I can Autosum the row with all of the monthly totals on on row?
Referencing the uploaded image, I would like to be able to copy/paste a formula or use a macro that will pull down the last value in each column (C-U) to row 3548 without having to manually use Autosum by scrolling from D3532 to D3548, E3546 to E3548, and so on. Note: the $ amounts on the image are false, I ain't rich!
I tried an Internet search, but most searches dealt with summing rows (left to right) and not summing columns. I tried rigging some of the example that I came across to handle columns, but encountered circular reference errors since I am trying to keep the last value in each column in the same column.
Thanks.