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

NYbills wrote:

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.

--

Dave Peterson

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.

"Dave Peterson" wrote:

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

NYbills wrote:

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.

--

Dave Peterson

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

"NYbills" wrote:

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.

Worked like a charm. Thanks.

"bpeltzer" wrote:

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

"NYbills" wrote:

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.