Sub FindAboveBelow()
Dim r As Range
MsgBox ("Above: " & VisibleAbove(Selection, 3).Value & vbCr & _
"Below: " & VisibleBelow(Selection, 3).Value)
End Sub
Function VisibleAbove(from As Range, nRows As Long) As Range
Dim allVisible As Range
Dim c As Range
Dim x As Long
Set VisibleAbove = from
Set allVisible = from.EntireColumn.SpecialCells(xlCellTypeVisible)
x = 0
For Each c In allVisible
x = x + 1
If c.Row = from.Row Then
Exit For
End If
Next c
x = x - nRows
For Each c In allVisible
x = x - 1
If x <= 0 Then
Set VisibleAbove = c
Exit Function
End If
Next c
End Function
Function VisibleBelow(from As Range, nRows As Long) As Range
Set VisibleBelow = from
Dim allVisible As Range
Dim c As Range
Dim x As Long
Dim y As Long
Set VisibleBelow = from
Set allVisible = from.EntireColumn.SpecialCells(xlCellTypeVisible)
x = 0
y = -1
For Each c In allVisible
x = x + 1
If c.Row = from.Row Then
y = x + nRows
ElseIf y > 0 Then
Set VisibleBelow = c
If x = y Then Exit Function
End If
Next c
End Function
Sub Macro1()
Dim v As Range
Set v = Range("A2:G" & Range("A65536").End(xlUp).
MsgBox "The cells are " & v.Address
End Sub
Taken from: http://www.ozgrid.com/forum/showthread.php?t=15629