Solved

Finding the nth visible row above a given row

Posted on 2012-12-26
6
356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

734 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