Forum Home
Press F1
Thread ID: 55501	2005-03-12 04:38:00	Excel formula to show row/cell visible status	BPW (7575)	Press F1

Post ID	Timestamp	Content	User
333334	2005-03-12 04:38:00	:help: Team I'm trying to put summary data at the top of a spreadsheet that uses a filtered list of products. The Subtotal formula gives me data on all of the visible data but I wanted further summary data based on the values in other fields, in this case product status (A, I or D), for only the visible data. Is there is an excel formula that shows if the row/cell is visible, if so I can concatenate this with the product status and sumif/countif at leisure. Any ideas?	BPW (7575)
333335	2005-03-12 10:04:00	Welcome to the board. :) Im not aware of any built-in function that will return a value indicating whether its visible or not. However, I have created a custom function for you that will do this. Syntax =RNGVISIBLE(Rng, [Criteria]) Argument Description Rng : Mandatory. The cells you wish to evaluate (eg A1:A5) Criteria : Optional. A text expression representing the criteria you require in addition to the cell being visible. You must include quotes (eg ">20") Use The RNGVISIBLE function returns an array of values. The RNGVISIBLE function is designed to be used within other functions so is no use by itself. However, it is reasonably versatile and you can use it inside most functions. Unfortunately it doesnt work with SUMIF/COUNTIF so thats why Ive added the ability to add your own criteria. This is a volatile formula which means it doesnt calculate unless forced so ensure you calculate the workbook before relying upon the results. Example Say you want to sum all cells in A1:A5 that are greater than 2. =SUM(rngvisible(A1:A5,">1")) Say you want to average cells in A1:A5 that equal 2. =COUNT(rngvisible(A1:A5,"=2"))/COUNT(rngvisible(A1:A5)) Note: Because the array returns zero for blank cells you use 2 counts instead of the average formula. To add the code below to a Module do the following:- 1. Open the Visual Basic Editor (ALT+F11 or Tools|Macro|Visual Basic Editor from the menu) 2. Select Insert|Module from the menu 3. Paste the code in the right-hand window 4. Close the Visual Basic Editor (ALT+Q or File|Close and return to Microsoft Excel from the menu) Function RNGVISIBLE(ByVal Rng As Range, Optional Criteria As String = "") As Variant '//By Parry 12 March, 2005 '//Returns an array of values for visible cells only '//Note: Treats blanks as a zero Dim cell As Range, TmpArr() As Variant Application.Volatile For Each cell In Rng If Not cell.EntireRow.Hidden And _ Not cell.EntireColumn.Hidden Then On Error Resume Next ReDim Preserve TmpArr(UBound(TmpArr) + 1) If Err.Number <> 0 Then ReDim TmpArr(0) On Error GoTo 0 If Criteria = "" Then If IsEmpty(cell) Then TmpArr(UBound(TmpArr)) = 0 Else TmpArr(UBound(TmpArr)) = cell.Value End If Else On Error Resume Next If Evaluate(cell.Value & Criteria) = True Then If IsEmpty(cell) Then TmpArr(UBound(TmpArr)) = 0 Else TmpArr(UBound(TmpArr)) = cell.Value End If End If End If End If Next cell RNGVISIBLE = TmpArr End Function	Parry (5696)
333336	2005-03-12 19:41:00	Edit to above post. I made a mistake in one of the examples. It should read... Say you want to sum all cells in A1:A5 that are greater than 1. =SUM(RNGVISIBLE(A1:A5,">1"))	Parry (5696)
333337	2005-03-12 23:28:00	:thumbs: Thanks . DOH - I should have though of using a function - I've traditionally used them only from within VB, not from spreadsheet itself . I could easily test for visibility using this and add in a column as a calculated attribute to the data . But your solution is much more elegant and efficient (and opens my mind to a world of possibilities) . Kind Regards Bryan	BPW (7575)
333338	2005-03-13 03:12:00	No worries. Post your code if you have any problems. I find making custom functions quite frustrating at times as legit code that works in a function called from a procedure works fine but is not always the case if you use the same function in a sheet. I had to do the above code the long way as the simple special cells to locate visible cells wont work for me in a cell function (but does when called in a procedure) ... eg Function RNGVISIBLE(ByVal Rng As Range) Dim Target On Error Resume Next Set Target = Rng.SpecialCells(xlCellTypeVisible) If Err.Number = 0 Then Set RNGVISIBLE = Target End Function Sub Test() MsgBox WorksheetFunction.Sum(RNGVISIBLE(Range("A1:A3"))) End Sub	Parry (5696)
1