# 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
###### Who is Participating?

x
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.

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

``````    Dim HeaderRow
With Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.FormulaR1C1 = "=SUBTOTAL(9, R[-" & .Row - HeaderRow & "]C:R[-1]C)"
End With
``````
Finance 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
Financial ControlAuthor 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
Finance 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

Experts Exchange Solution brought to you by