VBA Function for Hidden Cells

[aggiepaul7]

Hey guys,

I am pretty good with Excel, but am new to programming with VBA. I am trying to make a function that will only show the value of visible cells (so I can have excel calculate a slope/offset of a filtered table. I made the function below, and it seems to work pretty well.

Option Explicit

Function Visible(x) As Variant
Application.Volatile

If x.Rows.Hidden Then
Visible = ""
ElseIf x.Columns.Hidden Then
Visible = ""
Else: Visible = x.Value
End If

End Function


However, when I try to use it in an array formula instead of with an individual cell (example, {=SUM(Visible(A1:A10))} ), it only evaluates the hidden property on the first cell. So if the first cell is hidden, all cells will be blank, and if the first cell is not hidden, all values are summed, regardless if some of the later cells are hidden.

I usually use the IF and IFERROR functions with the SUM functionto filter out values I don't want, and I assumed I could do the same with this custom function.

Anyone know why it only looks at the property for first cell? Thanks in advance for your help.

 

[Rick Rothstein]

Hey guys,

I am pretty good with Excel, but am new to programming with VBA. I am trying to make a function that will only show the value of visible cells (so I can have excel calculate a slope/offset of a filtered table. I made the function below, and it seems to work pretty well.

Option Explicit

Function Visible(x) As Variant
Application.Volatile

If x.Rows.Hidden Then
Visible = ""
ElseIf x.Columns.Hidden Then
Visible = ""
Else: Visible = x.Value
End If

End Function


However, when I try to use it in an array formula instead of with an individual cell (example, {=SUM(Visible(A1:A10))} ), it only evaluates the hidden property on the first cell. So if the first cell is hidden, all cells will be blank, and if the first cell is not hidden, all values are summed, regardless if some of the later cells are hidden.

I usually use the IF and IFERROR functions with the SUM functionto filter out values I don't want, and I assumed I could do the same with this custom function.

Are you aware of the SUBTOTAL function (if not, look it up in the help files)... it has an option that allows you to ignore hidden cells within a range for AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEVP, SUM, VAR AND VARP. For your SUM question, this would sum only the visible cells in the range A1:A10...

=SUBTOTAL(109,A1:A10)

 

[Rick Rothstein]

Are you aware of the SUBTOTAL function (if not, look it up in the help files)... it has an option that allows you to ignore hidden cells within a range for AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEVP, SUM, VAR AND VARP. For your SUM question, this would sum only the visible cells in the range A1:A10...

=SUBTOTAL(109,A1:A10)

However, if you really do want a UDF (user defined function) that returns an array values from all the visible cells within a specified range, the perhaps this function will work for you...

Function VisibleCells(Rng As Range) As Variant
  Dim Count As Long, Cell As Range, VC As Variant
  ReDim VC(1 To Rng.Count)
  For Each Cell In Rng
    If Not Cell.EntireRow.Hidden Then
      Count = Count + 1
      VC(Count) = Cell.Value
    End If
  Next
  ReDim Preserve VC(1 To Count)
  VisibleCells = VC
End Function

 

[aggiepaul7]

Thanks Rick!

I was aware of the SUBTOTAL function. I was just using SUM as an example. I would ideally like to calculate the SLOPE, INTERCEPT, and RSQ, and some other functions. Sorry I didn't specify before.

I could have tried to create individual formulas for each of the equations I would use (SLOPE, etc.), but I thought it made more sense to make a single UDF that I could insert into these other formulas.

For example I could have
cell C1 = Slope(VisibleCells(B1:B10),VisibleCells(A1:A10))

It looks like your formula works for this! Thanks a lot.