Link to home
Create AccountLog in
Avatar of Saqib Husain
Saqib HusainFlag for Pakistan

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
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
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

Avatar of Saqib Husain

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.
Sorry for taking so long

Works just fine. Only converted it to a function.

Thanks a lot.

Saqib