Finding the nth visible row above a given row

Saqib Husain, Syed
Saqib Husain, Syed used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
Here's an option:

Sub MoveUpN()

Const n As Long = 5
Dim lRow As Long, lRowLoop As Long, lLoopN As Long

lRow = Selection.Row


For lLoopN = 1 To n
    For lRowLoop = lRow - 1 To 1 Step -1
        If Rows(lRowLoop).Hidden = False Then
            lRow = lRowLoop
            Exit For
        End If
    Next lRowLoop
Next

Cells(lRow, Selection.Column).Select

End Sub

Open in new window

Commented:
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

Commented:
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

CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial