Create a Macro variable in Excel that appends a Subtotal formula once a filter has been applied?

Hi Guys, I have recorded a macro in an Excel spreadsheet that filters Row 1 and on the field "Book" (column H) chooses "ABSCBU", then subtotals the USD value column (Column J). I will run it monthly so inevitably the next time I run it, it will use the same fixed cell to subtotal as the previous time, which is wrong. How can I create a variable so it appends the subtotal after the filter is applied? Justin
JCutcliffeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
To find the last row, you could use something like this which inserts the formula below the data in column B...

    Dim HeaderRow
    HeaderRow = 1
    With Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        .FormulaR1C1 = "=SUBTOTAL(9, R[-" & .Row - HeaderRow & "]C:R[-1]C)"
    End With

Open in new window

0
Rob HensonFinance AnalystCommented:
Couple of questions:

1) Will the number of rows of data change?

If so will the new rows be inserted or will new data just be pasted in over the top of the existing data?

2) Where is the SUBTOTAL going?
The subtotal function using the 9 parameter will only sum the visible rows anyway so it doesn't matter if it is right at the bottom of the data, beyond the filter range.

Alternatively, if you insert a row above the data so that headers become row 2, you could insert the SUBTOTAL above the headers so that it looks at the whole column:

=SUBTOTAL(9,J:J)

This would have to be in column K for example, ie not in column J, else you would end up with a circular reference.

You could also make use of a Dynamic Named Range. In the Name Manager, create a Named Range and use the following in the Refers To:

Range Name:  USD_VALUES
=OFFSET(Sheet1!$J$1,0,0,COUNTA(Sheet1!$J:$J),1)

Then use =SUBTOTAL(9,USD_VALUES)

Thanks
Rob H
0
JCutcliffeAuthor Commented:
Hi Rob, the number of rows will change. New data will be posted over the old data on a monthly basis. I am uploading the file now. I must filter column C for the word "ABSCBU", and then column AJ for the word "CMBS" then subtotal column O. Could you write me the code to do that, using the subtotal formula above the formula in Range "N1" totalling column O?
DataABS.xls
0
Rob HensonFinance AnalystCommented:
If you are putting the SUBTOTAL in column N, you can just use:

=SUBTOTAL(9,O:O)

That will sum all visible values in column O.

Thanks
Rob
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.