Solved: Sum every other visible cell via VBA

[JimS]

In the attachment I have a small sample of the data that I need to sum.


I have removed a lot of the non essential code and data.


The actual data is about 100 rows deep but can get very wide.


I have some code that will hide the columns that are not required during the summing step, this code basically hides and unhides the columns based on the user’s selection criteria (code not included).


That all works fine.


I need to be able to total, by row, the visible cells and write the totals out in pre-determined cells.


I have a macro that does the summing and writes out the totals where I need them (see the SumData sub).


The trick is the data is made up of 2 columns (per entry), the data is in “pairs of factors” and I need to total for each of the individual factors.


The SumData sub does this but it is VERY slow as the data grows (up over 2 minutes).


There is a second sub in the attachment that will reference only the visible cells (see the CellsToSum sub).


Does anyone know if it is possible to combine the 2 Subs somehow so that the SumData code doesn’t have to look at each and every cell to determine if it’s visible?

I can’t figure out how to use the CellsToSum code to sum the correct “pairs” of data like the SumData code does.

Thanks for any helps and ideas…

JimS

[Kenneth Hobs]

There are two methods.

1. =Subtotal
2. Range(whatever).SpecialCells(xlVisible)

[mostafa90]

this is great

[JimS]

I need to sum every other cell of the visible range of a "row" - one row at at a time.

Not sure how to use Subtotal on a row and can't figure out how to do a sum using the Range(whatever).SpecialCells(xlVisible) on every other cell in the visible range on a row.

[p45cal]

try:[VBA]Sub CellsToSum()
Dim x As Long, Tot1 As Double, Tot2 As Double
Dim tempRange As Range, cll As Range
With Worksheets("Data1")
.Select 'not needed
'Number of Rows
For x = 5 To 14
'Set the tempRange to only the visible cells for the row (x)
Set tempRange = Range("Row_" & x).SpecialCells(xlCellTypeVisible)
Tot1 = 0: Tot2 = 0
For Each cll In tempRange.Cells ' Do summing here
' cll.Select
If cll.Column / 2 = Int(cll.Column / 2) Then
Tot2 = Tot2 + cll.Value
Else
Tot1 = Tot1 + cll.Value
End If
Next cll
.Cells(Range("Row_" & x).row, "E") = Tot1
.Cells(Range("Row_" & x).row, "F") = Tot2
Next x
End With
End Sub
[/VBA]

[JimS]

p45cal,

Thanks so much for your help...

JimS