How to place the last value in a column on the current active cell in the same column

ludoviclalo

New Member
Joined
Sep 26, 2022
Messages
5
Office Version
  1. 365
Platform
  1. 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.
 

Attachments

  • Sample Workbook.png
    Sample Workbook.png
    37.6 KB · Views: 15

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top