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

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

badrhino

This will find all of the rows that are not hidden. From there just add your code to include only the nth rows.

Sub Macro1()
Dim v As Range
Set v = Range("A2:G" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Cells
MsgBox "The cells are " & v.Address
End Sub

Taken from: http://www.ozgrid.com/forum/showthread.php?t=15629

tdlewis

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

Open in new window

Saqib Husain

ASKER

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

tdlewis

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

Saqib Husain

ASKER

Sorry for taking so long

Works just fine. Only converted it to a function.

Thanks a lot.

Saqib