Making Excel only use visible cells

I’m cleaning and preparing school test score data for later this week. We are ranking schools for each subject and grade level based on average test score. The somewhat annoying challenge in excel is getting the built-in RANK() function to work on filtered data. When trying to input a range of scores into the RANK function, excel is including the hidden rows. The solution is courtesy of Damon Ostrander (yeah, he’s a pianist as well as a VBA pro).

Function Vis(Rin As Range) As Range
    Dim Cell As Range
    Application.Volatile
    Set Vis = Nothing
    For Each Cell In Rin
        If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
            If Vis Is Nothing Then
                Set Vis = Cell
            Else
                Set Vis = Union(Vis, Cell)
            End If
        End If
    Next Cell
End Function

I very very rarely mess with VBA (though I should find some time to learn it), but this was just too perfect. All you have to do with this function is go from =RANK(a1, $a$1:$a$99) to =RANK(Vis(a1), Vis($a$1:$a$99)). Drag the auto fill down the column and Presto!

As always, if you have any ideas or know better ways to do this, leave me a note in the comments.