PLA_LTM
asked on
Dynamic autosum across macro
I want to be able to use autosum across, in a macro. the amount of rows will always be different.
I was able to use the following macro, to dynamically autosum a column:
For Each NumRange In Columns("I").SpecialCells( xlConstant s, xlNumbers).Areas
SumAddr = NumRange.Address(False, False)
NumRange.Offset(NumRange.C ount, 0).Resize(1, 6).Formula = "=SUM(" & SumAddr & ")"
c = NumRange.Count
Next NumRange
I would like to do the same in rows.
I've attached a PDF of the report I'm working with.
Please help
Example.pdf
I was able to use the following macro, to dynamically autosum a column:
For Each NumRange In Columns("I").SpecialCells(
SumAddr = NumRange.Address(False, False)
NumRange.Offset(NumRange.C
c = NumRange.Count
Next NumRange
I would like to do the same in rows.
I've attached a PDF of the report I'm working with.
Please help
Example.pdf
Of course the 5 should be the right row number.
ASKER
hmm this seems to work but if i have a varying number of rows, what do i do?
sometimes i may have 4 rows others can be in 10 or greater.
Just add more rows...
Range("5:10")
HTH
Range("5:10")
HTH
That might attach areas. Try
For i = 5 to 10
For Each NumRange In Rows(i).SpecialCells(xlConstants, xlNumbers).Areas
SumAddr = NumRange.Address(False, False)
NumRange.Offset(0, NumRange.Count).Resize(6, 1).Formula = "=SUM(" & SumAddr & ")"
c = NumRange.Count
Next NumRange
Next
ASKER
Thank you TommySzalapski for you code. I'm not sure if i'm implementing it correctly.
I can get it to Sum across but not for all my records. The "6" in "Resize(6, 1)" seems to limit only the summation of 6 records. Is there a variable i can use here to sum exactly the number of records? (whether it's 1 or 1000 records) (is this even possible?)
I've added an image of my table, in hopes to illustrate my issue better.
The total across will always appear in Column G.
The number of records can range any where from 1 to several thousand.
I can get it to Sum across but not for all my records. The "6" in "Resize(6, 1)" seems to limit only the summation of 6 records. Is there a variable i can use here to sum exactly the number of records? (whether it's 1 or 1000 records) (is this even possible?)
I've added an image of my table, in hopes to illustrate my issue better.
The total across will always appear in Column G.
The number of records can range any where from 1 to several thousand.
Ah. Now that I can see your data. Use this.
Dim i As Integer, lastrow As Integer
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
For Each NumRange In Rows(i).SpecialCells(xlConstants, xlNumbers).Areas
SumAddr = NumRange.Address(False, False)
NumRange.Offset(0, NumRange.Columns.Count - NumRange.Column + 2).Formula = "=SUM(" & SumAddr & ")"
c = NumRange.Count
Next NumRange
Next
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OMG!!! That worked perfectly!!!
Thank you,
Thank you,
ASKER
TommySzalapski, thank you for your help.
I have another, VBS question posted.
https://www.experts-exchange.com/questions/26624318/Separate-Documents-Macro.html
would you mind checking to see if you can assist me here as well?
Open in new window