Subtotaling variable data in a macro

[Joe]

Hi. Working on a macro subtotal in which the amount if data is
variable. Let me elaborate. I am creating a macro to organize a
report from data which is variable. While all my columns of data
remain constant (so it's easy to format and pretty up the report), the
amount of data in the columns changes. One day the data can be only
five rows long and other days, it can be 250 rows long. I have to sort
the data by Provider ID (numeric - column A) and Provider name (alpha -
column B)---this is no problem. I then need to subtotal the Amount
Paid (column K) for each Provider ID. These subtotals would be best
placed at the bottom of the report---the length of which is also
obvioulsy variable. How do I work with variable data in macros?
Thanks so much for your time and brain power!
Joe

 

[Dave Peterson]

I think you have a few choices.

First, you may want to consider putting the subtotals in Row 1--and shifting the
data down a row. One thing that's nice about that is that first row (and header
data in row 2) could be always visible (window|freeze panes) when you're
filtering/scrolling through the data.

Second, if you use data|subtotals, you can just let excel put the subtotal rows
where it wants.

Third, you could pick out a column that always has data in it if that row is
used. Then use that to find the last row.

Dim LastRow as long
with worksheets("sheet9999")
lastrow = .cells(.rows.count,"A").end(xlup).row
.cells(lastrow+1,"A").resize(1,15).formular1c1 _
= "=subtotal(9,r2c:r[-1]c)"
end with

Watch out for typos!