Saqib Husain

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

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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

ASKER

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

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

ASKER

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