asked on # Finding the nth visible row above a given row

There are hidden rows in the worksheet and other rows hidden by filtering. I need to get the nth visible row above and below any given row.

I need to do this using VBA

I need to do this using VBA

Microsoft Excel

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
As @badrhino suggested, SpecialCells will help you narrow down the visible cells, however, you also need to loop through the results as suggested by @nutsch.

```
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
```

Please forgive me for the delay. Please bear with me.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Take your time. Post another comment if you need additional help figuring it out.

Sorry for taking so long

Works just fine. Only converted it to a function.

Thanks a lot.

Saqib

Works just fine. Only converted it to a function.

Thanks a lot.

Saqib

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