How to SUM visible cells and exclude hidden rows and columns

If you have hidden rows only, you can use SUBTOTAL function. In other cases, you can use Excel macros. Here is a VBA function that do SUM for visible cells only. It doesn’t matter if hidden are rows or columns.

Function SumVisible(CRange As Object)
    Application.Volatile
    TotalSum = 0
    For Each cell In CRange
       If cell.Columns.Hidden = False Then
          If cell.Rows.Hidden = False Then
             TotalSum = TotalSum + cell.Value
          End If
       End If
    Next
    SumVisible = TotalSum
End Function

Example – no hidden cells

Sum range A1:C3 by the SumVisible function.

sum without hidden cells

Example – hidden row and column

SumVisible A1:C3 after hiding row no.2 and B column .SUM visible cells only

Note: If you hide or unhide rows or columns after you have entered the SumVisible function, you have to recalculate the worksheet. For example press F9 or do some changes in other cells.

Add a comment »One comment to this article

  1. How to skip hidden cells in Excel macro?

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress