Subtotal using LastRow of a different column

**dagindi** · 07-20-2010, 05:33 PM

Attached is test spreadsheet.

I am trying to insert a formula into a macro that will allow me to change the formula "K4=subtotal(9,K6:K225)" to "K4=subtotal(9,K6:LastRow based on column G)"

- Row 4 will always hold the formula
- Row 6 will always be the starting point for the subtotal.

I tried a bunch of different codes (see the modules attached) and cannot seem to get any of them to work. I am trying to avoid having to put K6:K65536

Side note... the formula in "K4" will be applied to other columns as well but once I get the above solved, I have the solution already to paste it across.

Any help or recommendation is greatly appreciated.

**TMS** · 07-20-2010, 06:26 PM

Close ;-)

Please Login or Register  to view this content.

Regards

**dagindi** · 07-20-2010, 10:22 PM

TMShucks,

Thanks so much for the response.

It took me a few more tries (and hours) and I came across another thread that I was able to disect to get me to something very close to what you provided below. You can click HERE to see the thread I used.

The code I worked off of is:

Please Login or Register  to view this content.

I ended up cutting out everything but:

Please Login or Register  to view this content.

I changed what needed to be changed to conform to my macro/worksheet but was still having problems.

I caved in and checked your response and found that the code you provided:

Please Login or Register  to view this content.

versus the one I copied and modified:

Please Login or Register  to view this content.

was the main problem.

I'm not sure why, but I am very grateful for your response which enabled me to finish this leg of the macro.

Thank you!!

**TMS** · 07-21-2010, 03:50 AM

You're welcome. Thanks for the feedback.

The "other" code that you copied is using column D to get the last row of data, whereas, you said column G had the most information ... problem 1.

And it looks as though the code you copied was adding the subtotal at the bottom rather than the top ... problem 2.

The two lines of code that you point to as the "main problem" do the same thing, just expressed differently ... as is common in VBA

But, you have your solution, so all is well.

Regards