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
LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?
 
nutschConnect With a Mentor 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

0
 
badrhinoCommented:
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
0
 
tdlewisCommented:
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

0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Saqib Husain, SyedEngineerAuthor Commented:
Please forgive me for the delay. Please bear with me.
0
 
tdlewisCommented:
Take your time. Post another comment if you need additional help figuring it out.
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Sorry for taking so long

Works just fine. Only converted it to a function.

Thanks a lot.

Saqib
0
All Courses

From novice to tech pro — start learning today.