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

Attached Images

	d6formula.jpg (20.1 KB, 23 views)
	c6formula.jpg (20.3 KB, 23 views)

Attached Files

sumvisiblerows.xlsm (17.5 KB, 5 views)