#1
|
|||
|
|||
Sum visible rows
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 A712 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 Last edited by soroush.kalantari; 06-18-2023 at 08:16 PM. |
#2
|
|||
|
|||
Interesting ... it all seems to work here.
|
#3
|
|||
|
|||
@Logit. See the attached pictures.
|
#4
|
|||
|
|||
Help me understand what I am missing. The SUMS are correct in your images and in the workbook here.
???? |
#5
|
||||
|
||||
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) |
#6
|
||||
|
||||
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 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 |
#7
|
|||
|
|||
@Logit. I expect the c6 formula to be “C6=C7+ C9 + C10 + C12. See the new picture attachment.
|
#8
|
|||
|
|||
Quote:
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. |
#9
|
|||
|
|||
Quote:
|
Tags |
formula, visible |
|
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 |