Referencing a Column in a Selected Range of Columns

[Rob G]

Hi,

Using VBA code, how could I select a bunch of columns that may or may not be
continuous, and return a sum (or any function) from each column?

For example, I select Columns A,B, D, and E. The procedure (maybe in a
msgbox) returns:
A total = 100
B total = 200
D total = 150
E total = 175

Thanks for your help.

-Rob

p.s. The specific problem I am having is returning which column I selected.

 

[Tom Ogilvy]

Sub ShowTotals()
Dim rng As Range, ar As Range
Dim col As Range, sStr as String
Set rng = Selection.EntireColumn
For Each ar In rng
For Each col In ar
sStr = sStr & Left(col.Address(0, 0), 1 - (col.Column > 26))
sStr = sStr & " total = " & Format(Application.Sum(col), "#,###.00")
sStr = sStr & vbNewLine
Next
Next

MsgBox sStr

End Sub

 

[Rob G]

That is just perfect!

Thank you!

Sub ShowTotals()
Dim rng As Range, ar As Range
Dim col As Range, sStr as String
Set rng = Selection.EntireColumn
For Each ar In rng
For Each col In ar
sStr = sStr & Left(col.Address(0, 0), 1 - (col.Column > 26))
sStr = sStr & " total = " & Format(Application.Sum(col), "#,###.00")
sStr = sStr & vbNewLine
Next
Next

MsgBox sStr

End Sub

--
Regards,
Tom Ogilvy



not

 

[Tom Ogilvy]

Actually, that should have been:

Sub ShowTotals()
Dim rng As Range, ar As Range
Dim col As Range
Set rng = Intersect(Cells, Selection.EntireColumn)
For Each ar In rng.Areas
For Each col In ar.Columns
sStr = sStr & Left(col.Address(0, 0), 1 - (col.Column > 26))
sStr = sStr & " total = " & Format(Application.Sum(col), "#,###.00")
sStr = sStr & vbNewLine
Next
Next

MsgBox sStr

End Sub