Macro for Automatic Subtotals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Would like to create a macro that will auto subtotal a A/R Collection report.
The data is pulled externally then I subtotal the information. I created a
macro but the problem that I have is the rows change everyday. For example,
today the reports shows 1184 rows tomorrow it can show either more or less
than this number. If I run this macro with the rows not being 1184 the
output is not the same.

How do I create the macro to know that when the last line with data is the
ending range/row? Any comments would be greatly appreciated.
 
Can you pick out a column that always has data in it if the row is used?

I'm gonna use column A.

dim LastRow as long
dim myRng as range

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row

'how many columns, I used Z:
set myrng = .range("A1:Z" & lastrow)
end with

myrng.subtotal....
 
How do you select the range when recording your macro? If you manually
highlighted the data, then the range is hard-coded w/in the macro. The
easiest solution (assuming that other steps in the macro don't depend on the
number of rows), is to let Excel determine the range dynamically.
Ex:
I'm guessing that you've got something like
Range("A1:N269").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(14), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Instead, select a specific starting point, and left Excel determing the
extents (the change is substituting the first three rows below for the first
row above):
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(14), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
--Bruce
 
All data comes from the external source so there is no data that is always
there. Thanks for your input. The first answer resolved the issue.
 
Worked like a charm. Thanks.

bpeltzer said:
How do you select the range when recording your macro? If you manually
highlighted the data, then the range is hard-coded w/in the macro. The
easiest solution (assuming that other steps in the macro don't depend on the
number of rows), is to let Excel determine the range dynamically.
Ex:
I'm guessing that you've got something like
Range("A1:N269").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(14), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Instead, select a specific starting point, and left Excel determing the
extents (the change is substituting the first three rows below for the first
row above):
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(14), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
--Bruce
Click to expand...
 
Back
Top