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.
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
I need to do this using VBA
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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).
MsgBox "The cells are " & v.Address
End Sub
Taken from: http://www.ozgrid.com/forum/showthread.php?t=15629
Sub Macro1()
Dim v As Range
Set v = Range("A2:G" & Range("A65536").End(xlUp).
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial