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