Sum visible rows

soroush.kalantari · #1 06-18-2023, 04:36 AM

I have some data in a excel file lying in different columns and rows, which I want to have the sum of visible data of each column in the row immediately before first row of data. (see the attached excel file, the data are in A7

12 and I want to A6 formula be “=A12 + A10 + A9 + A7”and so on. I have written following macro to do this job. The macro do the job for A6 and B6 but fails at C6 and D6. Can you guide me on this issue?

Option Explicit

Sub sumvisible()

Dim t As Range
Dim n As Integer
Dim i As Integer
Dim cell0 As Range
Dim cellvalue As Variant
Dim j As Integer
Dim t0 As Range
Set t0 = Selection

For j = 1 To t0.Columns.Count
Set t = t0.Columns(j)
Set cell0 = t.Cells(1, 1).Offset(-1, 0)

n = t.Rows.Count

t.Cells(1, 1).Select

If Selection.EntireRow.Hidden = False Then
cell0.Select

ActiveCell.FormulaR1C1 = "=R[1]C"
End If
For i = 2 To n
t.Cells(i, 1).EntireRow.Select
If Selection.EntireRow.Hidden = False Then
cell0.Select

cellvalue = Right(cell0.Formula, Len(cell0.Formula) - 1)

ActiveCell.FormulaR1C1 = "=R[" & i & "]C + " & cellvalue
ActiveCell.Replace What:="'", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Next i

With Selection.Font
.Color = -6279056
.TintAndShade = 0
End With
Next j
End Sub

Logit · #2 06-18-2023, 10:26 AM

Interesting ... it all seems to work here.

soroush.kalantari · #3 06-18-2023, 08:12 PM

@Logit. See the attached pictures.

Logit · #4 06-18-2023, 11:29 PM

Help me understand what I am missing. The SUMS are correct in your images and in the workbook here.

????

soroush.kalantari · #5 06-19-2023, 08:20 PM

Quote:

Originally Posted by Logit

Help me understand what I am missing. The SUMS are correct in your images and in the workbook here.

????

@Logit. I expect the c6 formula to be “C6=C7+ C9 + C10 + C12. See the new picture attachment.

p45cal · #6 06-19-2023, 02:04 PM

Why don't you use the built-in functions which deal with hidden/visible rows:
in D6:
=SUBTOTAL(109,D7:D12)
or:
=AGGREGATE(9,3,D7:D12)

soroush.kalantari · #7 06-19-2023, 08:22 PM

Quote:

Originally Posted by p45cal

Why don't you use the built-in functions which deal with hidden/visible rows:
in D6:
=SUBTOTAL(109,D7

12)
or:
=AGGREGATE(9,3,D7

12)

It is because my routine work is such that I am used to hide rows temporaly by “grouping tool” and when I unhide the rows, I don’t want to sum include privously hidden rows which now are visible.
But thank you for your answer. It can solve part of my problem and I learned from your answer some Built in excel function that I didn’t know before.

p45cal · #8 06-19-2023, 02:39 PM

A selection of macros to put the formulae in place:

Code:

Sub blah()
Selection.Rows(1).Offset(-1).Formula = "=SUBTOTAL(109," & Selection.Columns(1).Address(0, 0) & ")"
End Sub

Code:

Sub blah2()
With Selection.Rows(1).Offset(-1)
  .Formula = "=SUBTOTAL(109," & Selection.Columns(1).Address(0, 0) & ")"
  .Value = .Value 'optional line to convert the formulae to plain values.
End With
End Sub

The first macro will put fomulae in the cells and their values will adjust immediately to what's hidden/not hidden.

In the second macro, if you remove the .Value = .Value line the same as above,
but if you leave that line in, they'll become plain values and will not adjust to hidden/not hidden rows, unless the macro is run again.

edit:
and another, more akin to what you originally asked:

Code:

Sub blah4()
Set visRng = Selection.Columns(1).SpecialCells(xlCellTypeVisible)
Selection.Rows(1).Offset(-1).Formula = "=sum(" & visRng.Address(0, 0) & ")"
End Sub

soroush.kalantari · #9 06-19-2023, 09:00 PM

Quote:

Originally Posted by p45cal

A selection of macros to put the formulae in place:

Code:

Sub blah()
Selection.Rows(1).Offset(-1).Formula = "=SUBTOTAL(109," & Selection.Columns(1).Address(0, 0) & ")"
End Sub

Code:

Sub blah2()
With Selection.Rows(1).Offset(-1)
  .Formula = "=SUBTOTAL(109," & Selection.Columns(1).Address(0, 0) & ")"
  .Value = .Value 'optional line to convert the formulae to plain values.
End With
End Sub

The first macro will put fomulae in the cells and their values will adjust immediately to what's hidden/not hidden.

In the second macro, if you remove the .Value = .Value line the same as above,
but if you leave that line in, they'll become plain values and will not adjust to hidden/not hidden rows, unless the macro is run again.

edit:
and another, more akin to what you originally asked:

Code:

Sub blah4()
Set visRng = Selection.Columns(1).SpecialCells(xlCellTypeVisible)
Selection.Rows(1).Offset(-1).Formula = "=sum(" & visRng.Address(0, 0) & ")"
End Sub

Thank for your reply, blah4 can do what I want.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Add Rows to table that will include content controls of previous rows	bobsagat	Word VBA	20	01-27-2020 08:00 AM
VBA color of visible gridlines of tables word (with some merged cells in first rows)	Alvaro.passi	Word VBA	0	07-18-2017 09:11 AM
How to find duplicate rows in a huge excel file containing 500000 rows	Stc25234	Excel Programming	2	06-23-2017 10:51 AM
Delete All empty Rows - Print - Undo all Rows deleted	Bathroth	Word VBA	1	10-01-2014 01:40 PM
Grouping table rows to prevent individual rows from breaking across pages	dennist77	Word	1	10-29-2013 11:39 PM