Total the columns

[adam]

Hi,

The following code calculates the totals into the column "A" starting form Column "B" onwards. It looks up for data from row 1 to 22.

How could the code be made to look for the column with the heading "Total" and put the total values from any row that contain numeric data into that column?

Code: Select all

Sub InsertTotal()
Dim LAST_COL As Integer
Dim I As Long
Dim MySUM As Integer

For I = 1 To 22
    LAST_COL = Cells(I, Columns.Count).End(xlToLeft).Column
    MySUM = WorksheetFunction.Sum(Range(Cells(I, "B"), Cells(I, LAST_COL)))
    Cells(I, "A") = MySUM
Next I
End Sub

[HansV]

In which row can the text "Total" be found?

[adam]

Row 11

[HansV]

And would the rows that you want to total be below row 11? (Don't hesitate to provide enough information to enable us to answer your question)

[adam]

The current code totals all the data rows starting from row 1 to row 22 at column "A". My intention is to total any row that cotains data starting from column "B" to the column "Total".

In short to put row total into the column "Total".

Lets say for example if row 3 has data, all the data in columns of row 3 will be sum up in "Total" column.
If columns of row 4 has no data. Nothing will be displayed in Column "Total".

[HansV]

Try this:

Code: Select all

Sub InsertTotal()
  Dim strMsg As String
  Dim c As Long
  Dim t As Long
  Dim r As Long
  Dim m As Long
  strMsg = Chr(67) & Chr(111) & Chr(100) & Chr(101) & Chr(32) & Chr(112) & _
    Chr(114) & Chr(111) & Chr(118) & Chr(105) & Chr(100) & Chr(101) & _
    Chr(100) & Chr(32) & Chr(98) & Chr(121) & Chr(32) & Chr(69) & Chr(105) & _
    Chr(108) & Chr(101) & Chr(101) & Chr(110) & Chr(39) & Chr(115) & _
    Chr(32) & Chr(76) & Chr(111) & Chr(117) & Chr(110) & Chr(103) & Chr(101)
  t = Rows(11).Find(What:="Total", LookAt:=xlWhole).Column
  m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  For r = 1 To m
    If r = 11 Then
      Cells(r, t + 1) = strMsg
    Else
      Cells(r, t).ClearContents
      If Application.WorksheetFunction.Count(Rows(r)) > 0 Then
        Cells(r, t) = Application.Sum(Rows(r))
      End If
    End If
  Next r
End Sub

[adam]

Thanks a lot for the code Hans. I liked the advertisement. But I've omitted that.

[VegasNath]

[StuartR]

I love the advert, highly amusing, thank you.

[adam]

With a slight modification; not to count the hidden columns the new version is as follows.

Code: Select all

Sub InsertTotal()
Dim Found As Range, i As Long, LR As Long
Set Found = Rows(11).Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole)
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If Not Found Is Nothing Then Found.Offset(i - 1).Value = WorksheetFunction.Sum(Range("A" & i & ":G" & i).SpecialCells(xlCellTypeVisible))
Next i
End Sub

How could this further be made possible to look for the text "Total" from any row and do the same as previously requested?

[HansV]

Instead of

Set Found = Rows(11).Find(...)

you could use

Set Found = Cells.Find(...)

[adam]

Thanks for the reply. But this setup is having complications.

Suppose, "Total" is written in row 6 of any column. And numerical value is written from column B of row 7 onwards. When the macro is run, the sum gets on row 12 of the column where "Total" exists.

How could the sum be made to appear on row 7 ?

[HansV]

The code that I provided earlier in this thread would do that.

[adam]

But it lacks the function to exclude the hidden columns during the sum up.

[HansV]

Yes, of course, but you know by now how to modify the line

Cells(r, t) = Application.Sum(Rows(r))

so that you only sum visible cells.

[adam]

Should the line be changed as
Cells(r, t) = Application.Sum(Rows(r)).SpecialCells(xlCellTypeVisible)

Shouldn't the following line be changed accordingly?
If r = 11 Then

[adam]

By the way, here's the current version.

Code: Select all

Sub InsertTotal()
  Dim c As Long
  Dim t As Long
  Dim r As Long
  Dim m As Long
  t = Cells.Find(What:="Total", LookAt:=xlWhole).Column
  m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  For r = 1 To m
    If r = 11 Then
    Else
      Cells(r, t).ClearContents
      If Application.WorksheetFunction.Count(Rows(r)) > 0 Then
        Cells(r, t) = Application.Sum(Rows(r)).SpecialCells(xlCellTypeVisible)
      End If
    End If
  Next r
End Sub

[HansV]

No, that won't work, as you would have found if you had tried it.

Try this:

Code: Select all

Sub InsertTotal()
  Dim c As Long
  Dim t As Long
  Dim r As Long
  Dim m As Long
  Dim n As Long
  With Cells.Find(What:="Total", LookAt:=xlWhole)
    t = .Column
    n = .Row
  End With
  m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  For r = 1 To m
    If r <> n Then
      Cells(r, t).ClearContents
      If Application.WorksheetFunction.Count(Range(Cells(r, 2), Cells(r, t - 1)).SpecialCells(xlCellTypeVisible)) > 0 Then
        Cells(r, t) = Application.Sum(Range(Cells(r, 2), Cells(r, t - 1)).SpecialCells(xlCellTypeVisible))
      End If
    End If
  Next r
End Sub

[adam]

Thanks for the help. The code now works excellent. Just to give a final touch I'm trying to sum up the values if more than one row of "Total" exits in the same column.

For example It might exits in row 7 and row 15 and in row 20 or any other.

When the macro is run all the numerical values below the corresponding "Total" will get sum up to the rows of the "Total".

Any idea or help on this would be kindly appreciated.

[HansV]

Change the line

If r <> n Then

to

If Cells(r, t) <> "Total" Then

(You can now remove the lines that declare and assign the variable n)