Can Worksheet Functions Operate On Visible Cells Only?

  • Thread starter Thread starter dan.boxall
  • Start date Start date
D

dan.boxall

I currently have a formula in a cell which gives me a
value based on data in various columns. What I would
like is for this value to change automatically when I use
autofilter to get a subset of my data. i.e. For it to
only calculate based on the visible cells.

Any help much appreciated.
 
I was looking for something similar. I wanted to sum only visible cells
after I had hidden unneeded columns. I found the following custom
function which can be added to Excel using the VBA editor.

I accessed the editor using Alt F-11 then from the menu bar I chose
Insert Module
then entered the following to create the new function,
Sum_Visible_Cells

This function will sum only those cells visible, no hidden or filtered
cells will be shown. At least it worked fine for me.

Function Sum_Visible_Cells(Cells_To_Sum As Object)
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function
 
Back
Top