Part way there.

Twelve groups of 4 columns with a blank column between each group and a two column group at the end.

Each column in each group will have the same number of rows and that number will change often.

Each group will have a different number of rows from the other groups but again always the same number of rows within the group.

The code below puts the sum formula properly at the end of each column and in each group (except the two column group which I will probably write a separate line bit code to take care of them).

The LastRow is giving me fits as I need it to refer to each group as the For iI loop does it looping.

Second major problem is getting the formula to refer to proper columns as it loops. As it is, it refers only to the first group.

The commented out LastRow works but only refers to the first group.

Thanks,
Howard

Sub SumMyCols()

Dim i As Long
Dim LastRow As Long, myCol As Long, iI As Long
Dim sumRng As Range

'("AB:AE, AG:AJ, AL:AO, AQ:AT, AV:AY, BA:BD, BF:BI, BK:BN, BP:BS, BU:BX, BZ:CC, CE:CH, CJ:CK")

'LastRow = Range("AB:AE").Find(What:="*", after:=[ab8], _
searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row

' MsgBox LastRow


For iI = 28 To 86

LastRow = Range(Cells(9, iI)).Find(What:="*", after:=Range(Cells(8, iI)), _
searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row

Cells(LastRow + 2, iI).Resize(1, 4) = Application.WorksheetFunction.Sum(Range("AB9:AB" & LastRow))
iI = iI + 4

Next 'iI

End Sub

Are the rows in each group contiguous? If so you just need to find the
last data row (sound familiar?) and exit the loop at the 1st empty
value.

OR

Use an array's 2nd dim to determine where empty cols are and where last
data row is for each col of UsedRange.

OR

Sub SumMyCols()
' Totals each col in sets of grouped cols
Dim n&, lRow&

For n = Range("AB1").Column To Range("CK1").Column
lRow = WorksheetFunction.CountA(Columns(n))
If lRow 0 Then _
Cells(lRow + 1, n) = WorksheetFunction.Sum(Columns(n))
Next 'n
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard,

Am Wed, 3 Dec 2014 19:17:09 -0800 (PST) schrieb L. Howard:

Twelve groups of 4 columns with a blank column between each group and a two column group at the end.

Each column in each group will have the same number of rows and that number will change often.

Each group will have a different number of rows from the other groups but again always the same number of rows within the group.

The code below puts the sum formula properly at the end of each column and in each group (except the two column group which I will probably write a separate line bit code to take care of them).

you also can set a rngBig and loop through all columns of this range:

Sub mySum()
Dim rngBig As Range, myCol As Range
Dim LRow As Long, ColNR As Long

With Sheets("Sheet1")
Set rngBig = Union(.Range("AB:AE"), .Range("AG:AJ"), .Range("AL:AO"),
_
.Range("AQ:AT"), .Range("AV:AY"), .Range("BA:BD"),
..Range("BF:BI"), _
.Range("BK:BN"), .Range("BP:BS"), .Range("BU:BX"),
..Range("BZ:CC"), _
.Range("CH:CE"), .Range("CJ:CK"))
For Each myCol In rngBig.Columns
LRow = .Cells(Rows.Count, myCol.Column).End(xlUp).Row
ColNR = myCol.Column
.Cells(LRow + 1, ColNR).Formula = "=Sum(" & Cells(1,
ColNR).Address & ":" & _
Cells(LRow, ColNR).Address & ")"
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Very nice as usual.

This works on my example with the small modification to write the totals below the data.

The data starts in row 9 w/ headers and formula-produced values from there on down.


Sub SumMyColsGarry()
' Totals each col in sets of grouped cols
Dim n&, lRow&

For n = Range("AB9").Column To Range("CK9").Column
lRow = WorksheetFunction.CountA(Columns(n))

If lRow 0 Then _
Cells(Rows.Count, n).End(xlUp)(3) = WorksheetFunction.Sum(Columns(n))

Next 'n
End Sub



<the rows in each group contiguous? If so you just need to find the
last data row (sound familiar?) and exit the loop at the 1st empty
value.

Yes to this question, contiguous or none at all from row nine on down.

If I read it correctly, that was what I was trying to do which I could make work with the first group, but was hung up on finding the 1st empty for each group as the loop continued.


<Use an array's 2nd dim to determine where empty cols are and where last
data row is for each col of UsedRange.

This method puts me in my twilight zone of arrays were I seem to be stuck forever. I would like to see an example of how that would work on the column group I described here.

12 - 4 columns groups with one column space between each group, with a 2 column group at the end. Headers in row 9, data in contiguous rows, rows vary in number by group, but are the same within the group.

You have given me something that works, so this last part can surely be a 'time permitting' thing.

Thanks,
Howard

Hi again,

Am Thu, 4 Dec 2014 08:27:43 +0100 schrieb Claus Busch:

Sub mySum()

or try:

Sub mySum2()
Dim i As Long, LRow As Long

With Sheets("Sheet1")
For i = 28 To 89
If i Mod 5 < 2 Then
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(LRow + 1, i).Formula = "=SUM(" & Cells(1, i).Address & _
":" & Cells(LRow, i).Address & ")"
End If
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Sub SumMyColsGarry()
' Totals each col in sets of grouped cols
Dim n&, lRow&

For n = Range("AB9").Column To Range("CK9").Column
lRow = WorksheetFunction.CountA(Columns(n))

If lRow 0 Then _
Cells(Rows.Count, n).End(xlUp)(3) =
WorksheetFunction.Sum(Columns(n))

Next 'n
End Sub

If rows are contiguous then and you want to place the sum 3 rows below
then...

If lRow 0 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

...works as well without the extra processing!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

you also can set a rngBig and loop through all columns of this range:

Sub mySum()
Dim rngBig As Range, myCol As Range
Dim LRow As Long, ColNR As Long

With Sheets("Sheet1")
Set rngBig = Union(.Range("AB:AE"), .Range("AG:AJ"), .Range("AL:AO"),
_
.Range("AQ:AT"), .Range("AV:AY"), .Range("BA:BD"),
.Range("BF:BI"), _
.Range("BK:BN"), .Range("BP:BS"), .Range("BU:BX"),
.Range("BZ:CC"), _
.Range("CH:CE"), .Range("CJ:CK"))
For Each myCol In rngBig.Columns
LRow = .Cells(Rows.Count, myCol.Column).End(xlUp).Row
ColNR = myCol.Column
.Cells(LRow + 1, ColNR).Formula = "=Sum(" & Cells(1,
ColNR).Address & ":" & _
Cells(LRow, ColNR).Address & ")"
Next
End With
End Sub


Regards
Claus B.

Hi Claus,

That looks pretty heavy duty, I'll give it a try.

Thanks.
Howard

Oops! ..forgot the start row...

If lRow 8 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

If rows are contiguous then and you want to place the sum 3 rows below
then...

If lRow 0 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

..works as well without the extra processing!

--
Garry


You'd get a chuckle looking at the examples I tried to get that done.

Thanks again.

Howard

Here's an option I'd be likely to go with instead of using VBA...

Select any cell in row 2, say A2.
Add a defined name with local scope as follows:
Name:="LastCell"
RefersTo:=A1

Select the 1st column to receive a total, say AE.
Enter the following formula:
=SUM(AE$9:LastCell)
Drag-copy the cell (or blocks) to anywhere you need a sum.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Oops again!

Here's an option I'd be likely to go with instead of using VBA...

Select any cell in row 2, say A2.
Add a defined name with local scope as follows:
Name:="LastCell"

RefersTo:="=A1"

Select the 1st column to receive a total, say AE.
Enter the following formula:
=SUM(AE$9:LastCell)
Drag-copy the cell (or blocks) to anywhere you need a sum.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Wow, lots of option coming faster than I can try them.

This below is what I am using at present. I'll check out the other options as soon as I can.

With the new option suggested (UNcommented in the code) does not produce totals.

Your original, which I modified, works fine. (it is commented OUT as is the code to delete the totals during testing etc.)

Does it matter if these ranges are AB8 & CK8 as opposed to AB1 & CK1?
Range("AB8").Column To Range("CK8").

Howard


Sub SumMyColsGarry()
' Totals each col in sets of grouped cols
Dim n&, lRow&

For n = Range("AB8").Column To Range("CK8").Column
lRow = WorksheetFunction.CountA(Columns(n))

'/ No totals ???
If lRow 8 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

'/ Works okay
' If lRow 0 Then _
Cells(Rows.Count, n).End(xlUp)(3) = WorksheetFunction.Sum(Columns(n))

'/ Used to delete totals for testing
' If lRow 0 Then _
Cells(Rows.Count, n).End(xlUp)(1) = ""
Next 'n

End Sub

Hi howard,

Am Thu, 4 Dec 2014 08:35:47 +0100 schrieb Claus Busch:

Sub mySum2()

if you want values instead of formulas try:

Sub mySum3()
Dim First As Range, Last As Range
Dim i As Long

With Sheets("Sheet1")
For i = 28 To 89
If i Mod 5 < 2 Then
Set First = .Cells(9, i)
Set Last = .Cells(Rows.Count, i).End(xlUp)
Last.Offset(1, 0) = WorksheetFunction.Sum(.Range(First, Last))
End If
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Thursday, December 4, 2014 12:50:55 AM UTC-8, Claus Busch wrote:
Hi howard,

Am Thu, 4 Dec 2014 08:35:47 +0100 schrieb Claus Busch:

Sub mySum2()

if you want values instead of formulas try:

Sub mySum3()
Dim First As Range, Last As Range
Dim i As Long

With Sheets("Sheet1")
For i = 28 To 89
If i Mod 5 < 2 Then
Set First = .Cells(9, i)
Set Last = .Cells(Rows.Count, i).End(xlUp)
Last.Offset(1, 0) = WorksheetFunction.Sum(.Range(First, Last))
End If
Next
End With
End Sub


Regards
Claus B.

Thanks, values will probably be best.

Yet to test all the options.

That is always fun and interesting to do so.

Thanks tons.

Howard

Hi Howard,

Am Wed, 3 Dec 2014 19:17:09 -0800 (PST) schrieb L. Howard:

Cells(LastRow + 2, iI).Resize(1, 4) = Application.WorksheetFunction.Sum(Range("AB9:AB" & LastRow))

if you write a formula in a range the references will be modified. A
worksheetfunction does not do so. You can use formulas and change them
to values:

Sub mySum4()
Dim First As Range, Last As Range, rngS As Range
Dim i As Long

With Sheets("Sheet1")
For i = 28 To 88 Step 5
Set First = .Cells(9, i)
Set Last = .Cells(Rows.Count, i).End(xlUp)
Set rngS = Last.Offset(1, 0).Resize(1, IIf(i < 88, 4, 2))
With rngS
.Formula = "=SUM(" & Range(First, Last).Address(0, 0) & ")"
.Value = .Value
End With
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

You're losing me! This works as tested per your description of
layout...

Sub SumMyCols()
' Totals each col in sets of grouped cols
Dim n&, lRow&

For n = Range("AE1").Column To Range("CK1").Column
lRow = WorksheetFunction.CountA(Columns(n))
If lRow 8 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))
Next 'n
End Sub

...where, as you state, the data in each column is contiguous. I assumed
that also refers to rows 1 thru 8!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry:

Am Thu, 04 Dec 2014 04:49:52 -0500 schrieb GS:

If lRow 8 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

If lRow 8 Then _
Cells(lRow + 8, n) = WorksheetFunction.Sum(Columns(n))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Thu, 4 Dec 2014 10:57:21 +0100 schrieb Claus Busch:

If lRow 8 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

If lRow 8 Then _
Cells(lRow + 8, n) = WorksheetFunction.Sum(Columns(n))

sorry, my bad.
If there are values in the first 8 rows you are right


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Thu, 4 Dec 2014 10:57:21 +0100 schrieb Claus Busch:

If lRow 8 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

If lRow 8 Then _
Cells(lRow + 8, n) = WorksheetFunction.Sum(Columns(n))

sorry, my bad.
If there are values in the first 8 rows you are right


Regards
Claus B.

The totals are placed 3 rows below the last value to be summed!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

<..where, as you state, the data in each column is contiguous. I assumed
that also refers to rows 1 thru 8!

This was in one of my replies, should have been more explicit and in my first post, an 'oops on my part. Sorry.

<The data starts in row 9 w/ headers and formula-produced values from there on down.


The headers are in row 8 and data starts in row 9 and is contiguous.

Above that is blank, to the best of my knowledge.

Howard

Hi Howard,

Am Thu, 4 Dec 2014 03:32:05 -0800 (PST) schrieb L. Howard:

<The data starts in row 9 w/ headers and formula-produced values from there on down.

if the rows above row 8 are empty or have text you can use the whole
column because SUM ignores text.
To get the result 3 rows under the last row you have to change the 3 to
11 if cells in rows(1:7) are blank:
If lRow 8 Then _
Cells(lRow + 11, n) = WorksheetFunction.Sum(Columns(n))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

if the rows above row 8 are empty or have text you can use the whole
column because SUM ignores text.
To get the result 3 rows under the last row you have to change the 3 to
11 if cells in rows(1:7) are blank:
If lRow 8 Then _
Cells(lRow + 11, n) = WorksheetFunction.Sum(Columns(n))


Regards
Claus B.
--

Aha, I'll give that a go.

Actually the (3) I have been using only puts one blank row above the totals.

Also, how would I capture all those total cells in a With Statement so I can format them all at once?

Would want .Font Bold = true
.Interior.colorindex = 17
.Top border = xlMedium
.Bottom border = xlMedium

This is turning out much more complicated than I first thought.

Howard

Hi Howard,

Am Thu, 4 Dec 2014 05:18:21 -0800 (PST) schrieb L. Howard:

Would want .Font Bold = true
.Interior.colorindex = 17
.Top border = xlMedium
.Bottom border = xlMedium

try it this way:

Sub SumMyCols()
' Totals each col in sets of grouped cols
Dim n&, lRow&
Application.ScreenUpdating = False

For n = 28 To 89
If n Mod 5 < 2 Then
lRow = Cells(Rows.Count, n).End(xlUp).Row
With Cells(lRow + 3, n)
.Value = WorksheetFunction.Sum(Columns(n))
.Font.Bold = True
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
End With
End If
Next 'n
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Thu, 4 Dec 2014 14:37:23 +0100 schrieb Claus Busch:

sorry forgot the interior.colorindex:

Sub SumMyCols()
' Totals each col in sets of grouped cols
Dim n&, lRow&
Application.ScreenUpdating = False

For n = 28 To 89
If n Mod 5 < 2 Then
lRow = Cells(Rows.Count, n).End(xlUp).Row
With Cells(lRow + 3, n)
.Value = WorksheetFunction.Sum(Columns(n))
.Font.Bold = True
.Interior.ColorIndex = 17
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
End With
End If
Next 'n
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Thursday, December 4, 2014 5:40:33 AM UTC-8, Claus Busch wrote:
Hi again,

Am Thu, 4 Dec 2014 14:37:23 +0100 schrieb Claus Busch:

sorry forgot the interior.colorindex:

Sub SumMyCols()
' Totals each col in sets of grouped cols
Dim n&, lRow&
Application.ScreenUpdating = False

For n = 28 To 89
If n Mod 5 < 2 Then
lRow = Cells(Rows.Count, n).End(xlUp).Row
With Cells(lRow + 3, n)
.Value = WorksheetFunction.Sum(Columns(n))
.Font.Bold = True
.Interior.ColorIndex = 17
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
End With
End If
Next 'n
Application.ScreenUpdating = True
End Sub


That's a winner, indeed. I caught the color and also added alignment center

Look really good.

Thanks much.

Howard

if the rows above row 8 are empty or have text you can use the whole
column because SUM ignores text.
To get the result 3 rows under the last row you have to change the 3
to 11 if cells in rows(1:7) are blank:
If lRow 8 Then _
Cells(lRow + 11, n) = WorksheetFunction.Sum(Columns(n))


Regards
Claus B.
--

Aha, I'll give that a go.

Actually the (3) I have been using only puts one blank row above the
totals.

Also, how would I capture all those total cells in a With Statement
so I can format them all at once?

Would want .Font Bold = true
.Interior.colorindex = 17
.Top border = xlMedium
.Bottom border = xlMedium

This is turning out much more complicated than I first thought.

Howard

You can also preformat that cell with the formula as formatting copies
with the cell! I assume you know how that works (Ctrl+drag)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

You can also preformat that cell with the formula as formatting copies
with the cell! I assume you know how that works (Ctrl+drag)!


Hmmm, I'm wondering if would recognize that if I saw it.

Are you meaning something like cell.value = 100 .format.currency or something like that?

I'll take a google at it to see what I can come up with.

Howard

You can also preformat that cell with the formula as formatting
copies with the cell! I assume you know how that works (Ctrl+drag)!


Hmmm, I'm wondering if would recognize that if I saw it.

Are you meaning something like cell.value = 100 .format.currency or
something like that?

I'll take a google at it to see what I can come up with.

Howard

No! I mean my suggestion to add defined name 'LastCell' and use it as
stated (drag-copy). The formatting will tag along!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thursday, December 4, 2014 12:08:13 PM UTC-8, GS wrote:

You can also preformat that cell with the formula as formatting
copies with the cell! I assume you know how that works (Ctrl+drag)!


Hmmm, I'm wondering if would recognize that if I saw it.

Are you meaning something like cell.value = 100 .format.currency or
something like that?

I'll take a google at it to see what I can come up with.

Howard

No! I mean my suggestion to add defined name 'LastCell' and use it as
stated (drag-copy). The formatting will tag along!

Here's an option I'd be likely to go with instead of using VBA...

Select any cell in row 2, say A2.
Add a defined name with local scope as follows:
Name:="LastCell"
RefersTo:=A1

Select the 1st column to receive a total, say AE.
Enter the following formula:
=SUM(AE$9:LastCell)
Drag-copy the cell (or blocks) to anywhere you need a sum.


You are referring to this post.

This puzzles me. Not understanding the full technique to make that happen.

Here is a link to a test sheet that pretty much does all the stuff.

I would be interested in the named range caper if you have time to demo it on this sheet.

https://www.dropbox.com/s/lriboaq8lx...otal.xlsm?dl=0

I'm happy with the existing until I get feedback from user.

And if you have more pressing issues I understand.

Thanks.
Howard