Improve company productivity with a Business Account.Sign Up

x
?
Solved

Finding the nth visible row above a given row

Posted on 2012-12-26
6
Medium Priority
?
466 Views
Last Modified: 2013-03-01
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
0
Comment
Question by:Saqib Husain, Syed
6 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 38721488
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
 
LVL 1

Expert Comment

by:badrhino
ID: 38721503
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
 
LVL 10

Expert Comment

by:tdlewis
ID: 38721617
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 38822697
Please forgive me for the delay. Please bear with me.
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38822967
Take your time. Post another comment if you need additional help figuring it out.
0
 
LVL 43

Author Closing Comment

by:Saqib Husain, Syed
ID: 38941279
Sorry for taking so long

Works just fine. Only converted it to a function.

Thanks a lot.

Saqib
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question